Strange conditional copying of spread sheet??

S

SaintJ

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

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.
 
S

SaintJ

Thanks, just wondering what a multicell array formula is?

Tom Ogilvy said:
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


SaintJ said:
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??
 
T

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

SaintJ said:
Thanks, just wondering what a multicell array formula is?

Tom Ogilvy said:
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


SaintJ said:
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??
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top