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