Strange conditional copying of spread sheet??
 

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??

Strange conditional copying of spread sheet??
 

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,I F(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


"SaintJ" wrote in message
...
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??

Strange conditional copying of spread sheet??
 

Thanks, just wondering what a multicell array formula is?

"Tom Ogilvy" wrote:

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,I F(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


"SaintJ" wrote in message
...
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??

Strange conditional copying of spread sheet??
 

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

"SaintJ" wrote in message
...
Thanks, just wondering what a multicell array formula is?

"Tom Ogilvy" wrote:

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,I F(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


"SaintJ" wrote in message
...
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??