Strange conditional copying of spread sheet??

[Guest]

Dear all. I have written a conditional to formula which copies a work sheet
(cell by cell) based on which work sheet is currently selected in a drop down
menu in another work sheet (plan). Either data from work sheet st1, st2 or
st3 is copied into the cell.

=IF(LOOKUP(TODAY(),plan!$B$2:$G$2,plan!$B$3:$G$3)="st1",st1!A1:C10,IF(LOOKUP(TODAY(),plan!$B$2:$G$2,plan!$B$3:$G$3)="st2",st2!A1:C10,IF(LOOKUP(TODAY(),plan!$B$2:$G$2,plan!$B$3:$G$3)="st3",st3!A1:C10,"")))

This code works, however you may notice that the cell reference to copy to
the single cell is a range of 9 cells if the conditional function 'IF' is
satisfied. I am a bit confused since it is some time since i wrote this code
and did not notice. Now when I try to change the [value if true] from any of
the IF functions to a single cell instead of a rang eof nine cells it does
not work....

Does anyone have an idea what I may have done??

 

[Tom Ogilvy]

the only way you formula would work in a single cell is to array enter it
with Ctrl+Shift+Enter. then it returns the value in the upper left corner
of your 30 cell range (A1 in each case). However, you can change it refer
to A1 directly.

=IF(LOOKUP(TODAY(),plan!$B$2:$G$2,plan!$B$3:$G$3)="st1",st1!A1,IF(LOOKUP(TOD
AY(),plan!$B$2:$G$2,plan!$B$3:$G$3)="st2",st2!A1,IF(LOOKUP(TODAY(),plan!$B$2
:$G$2,plan!$B$3:$G$3)="st3",st3!A1,"")))

However, when done editing it, then don't enter it with Ctrl+Shift+enter,
enter it with just Enter.

I worked fine for me.

This assumes this isn't a multicell array formula.

 

[Guest]

Thanks, just wondering what a multicell array formula is?

the only way you formula would work in a single cell is to array enter it
with Ctrl+Shift+Enter. then it returns the value in the upper left corner
of your 30 cell range (A1 in each case). However, you can change it refer
to A1 directly.

=IF(LOOKUP(TODAY(),plan!$B$2:$G$2,plan!$B$3:$G$3)="st1",st1!A1,IF(LOOKUP(TOD
AY(),plan!$B$2:$G$2,plan!$B$3:$G$3)="st2",st2!A1,IF(LOOKUP(TODAY(),plan!$B$2
:$G$2,plan!$B$3:$G$3)="st3",st3!A1,"")))

However, when done editing it, then don't enter it with Ctrl+Shift+enter,
enter it with just Enter.

I worked fine for me.

This assumes this isn't a multicell array formula.

--
Regards,
Tom Ogilvy

Dear all. I have written a conditional to formula which copies a work sheet
(cell by cell) based on which work sheet is currently selected in a drop down
menu in another work sheet (plan). Either data from work sheet st1, st2 or
st3 is copied into the cell.

=IF(LOOKUP(TODAY(),plan!$B$2:$G$2,plan!$B$3:$G$3)="st1",st1!A1:C10,IF(LOOKUP
(TODAY(),plan!$B$2:$G$2,plan!$B$3:$G$3)="st2",st2!A1:C10,IF(LOOKUP(TODAY(),p
lan!$B$2:$G$2,plan!$B$3:$G$3)="st3",st3!A1:C10,"")))

This code works, however you may notice that the cell reference to copy to
the single cell is a range of 9 cells if the conditional function 'IF' is
satisfied. I am a bit confused since it is some time since i wrote this code
and did not notice. Now when I try to change the [value if true] from any of
the IF functions to a single cell instead of a rang eof nine cells it does
not work....

Does anyone have an idea what I may have done??

 

[Tom Ogilvy]

Assume you selected a 10 row by 3 column area of cells to correspond to the
10 row by 3 column area being returned by your initial formula (A1:C10) and
then went into the formula bar and pasted you formula, then did
Ctrl+shift+enter

then if you want to each cell in that range you would see

{=formula}
where formula would be your formula. Each of the cells containing the
formula would return the result from the range being returned in the same
relative position. so if the upper left corner of where you place the
formula was B9, then B9 would return ST1!A1, and C9 would return ST1!B1 and
B10 would return ST1!A2 and so forth for example.

--
Regards,
Tom Ogilvy

Thanks, just wondering what a multicell array formula is?

the only way you formula would work in a single cell is to array enter it
with Ctrl+Shift+Enter. then it returns the value in the upper left corner
of your 30 cell range (A1 in each case). However, you can change it refer
to A1 directly.

=IF(LOOKUP(TODAY(),plan!$B$2:$G$2,plan!$B$3:$G$3)="st1",st1!A1,IF(LOOKUP(TOD
AY(),plan!$B$2:$G$2,plan!$B$3:$G$3)="st2",st2!A1,IF(LOOKUP(TODAY(),plan!$B$2
:$G$2,plan!$B$3:$G$3)="st3",st3!A1,"")))

However, when done editing it, then don't enter it with Ctrl+Shift+enter,
enter it with just Enter.

I worked fine for me.

This assumes this isn't a multicell array formula.

--
Regards,
Tom Ogilvy

Dear all. I have written a conditional to formula which copies a work sheet
(cell by cell) based on which work sheet is currently selected in a

drop
down

menu in another work sheet (plan). Either data from work sheet st1, st2 or
st3 is copied into the cell.

=IF(LOOKUP(TODAY(),plan!$B$2:$G$2,plan!$B$3:$G$3)="st1",st1!A1:C10,IF(LOOKUP
(TODAY(),plan!$B$2:$G$2,plan!$B$3:$G$3)="st2",st2!A1:C10,IF(LOOKUP(TODAY(),p
lan!$B$2:$G$2,plan!$B$3:$G$3)="st3",st3!A1:C10,"")))

This code works, however you may notice that the cell reference to copy to
the single cell is a range of 9 cells if the conditional function 'IF' is
satisfied. I am a bit confused since it is some time since i wrote

this
code

and did not notice. Now when I try to change the [value if true] from

any
of

the IF functions to a single cell instead of a rang eof nine cells it does
not work....

Does anyone have an idea what I may have done??