Replies

Select the column (say it’s D)
Use Format/Conditional Format
Change “Cell Value Is” to “Formula Is”
Assuming D1 is active, enter
=$C1=whatever your test is, like
=NOT(ISBLANK($C1))
Then click FOrmat button & do whatever you want.

No — G3 has the requirements built in to its formula. When G3 is true, its contents must be true which are your restraints.

Sheets(“Sheet1”).Range(“G3”).Value = DialogSheets(“Dialog1”).EditBoxes(“Edit Box 4”).Text

Let’s assume cell d7:d8 must have 1,2,3 and d9 must have 1,2,4. You can place this in a cell, say G3, which contains a formula:
=AND(OR(D7={1,2,3}),OR(D8={1,2,3}),OR(D9={1,2,4}))
then use solver to make sure G3 is TRUE.
HTH
Bob Umlas
Excel MVP

Because of what I passed to the MID function. If you look at help, the MID function’s second parameter is usually a number, NOT AN ARRAY OF NUMBERS. Like =MID(A1,5,3). If you pass an array to the function, like =MID(A1,{1,2,3},1) then Excel needs to know this and it’s via the array-entry that you tell it so.

Complain? Doesn’t sound like a complaint!
Top understand the formula, let’s assume A1 contains 10875.
=SUM(1*MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1))
LEN(A1) is 5
“1:LEN(A1) is now “1:5”
INDIRECT(“1:5”) is the range 1:5
ROW(1:5) is then {1;2;3;4;5}
MID(A1,{1;2;3;4;5},1) is
{“1″;”0″;”8″;”7″;”5”}
1 * that is
{1;0;8;7;5}
SUM({1;0;8;7;5}) is 21.
Does that help?

Wassim, Wassim — Thought you’d be an array-formula champ by now (folks — I know Wassim personally)
Try array-entering:
=SUM(1*MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1))

If you didn’t turn it off, it’s on each time you start Excel. However, you can use alt/F11, then ctrl/G (brings up the “immediate” pane window), then simply type and enter
Application.EnableEvents = True
then Alt/Q to return to Excel.

Maybe you have events disabled?
Put this piece of code somewhere where you know it’ll run:
Application.EnableEvents = True

Yes, but I didn’t think the user wanted it to be so dynamic — was just providing another way.

Or in the Activate event:
Private Sub Worksheet_Activate()
On Error Resume Next
me.name=range(“A1”).Value
End Sub

if the date is in B2, then simply use =B2+90.

Sure! Assume rg is defined as A1:A5 and contains Apple, Pear,Banana,Orange,Grape and B1″B5 contains 10,20,30,40,50.
An expression like rg”Apple” would compare each item of rg against the string “Apple” and return something like {FALSE;TRUE;TRUE;TRUE;TRUE} where TRUE means that item of rg is NOT = “Apple”
TRUE * TRUE is 1, all other combinations produce 0.
So, multiplying these rg”whatever” is something like
{FALSE;TRUE;TRUE;TRUE;TRUE} * {TRUE;FALSE;TRUE;TRUE;TRUE} which is {0;0;1;1;1}.
Offset(Rg,,1) is the column of #s next to rg, or the 10,20,30,40,50. Multiplying it all out is like:
{10;20;30;40;50}*(0;1;1;1;1}*{1;1;0;1;1}*{1;1;1;0;1} which is {0;20;0;0;50}.
This is then passed into the SUM, for a result of 70.
Capisce?

Or ctrl/shift/enter:
=SUM(OFFSET(rg,,1)*(rg”Apple”)*(rg”Banana”)*(rg”Orange”))

Clearly a bug — change orientation to 88 degrees & it’t not bad. 89 degrees & it’s teeny! 90 degrees & it’s gone! Either use 88 or re-orient it!