Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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?? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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?? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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?? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copying spread sheet | Excel Discussion (Misc queries) | |||
Copying conditional formatting to entire sheet | Excel Discussion (Misc queries) | |||
Conditional Formatting Precidence when copying to new sheet in 200 | Excel Discussion (Misc queries) | |||
how do i enter a bull call spread into the options spread sheet ? | Excel Worksheet Functions | |||
Strange request Automaticaly send spread sheet as an e-mail | Excel Programming |