| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
today() bug
I have made a formula in which today() is used multiple times and it returnsa
with an error for no apparent reason? how can i overcome this one? formula- IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan!$C$3:$N$3)=" 3d Backs",' 3d Backs'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,pla n!$C$3:$N$3)="3d Locks",'3d Locks'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,pla n!$C$3:$N$3)="3d Props",'3d Props'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,pla n!$C$3:$N$3)="3d HBR",'3d HBR'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan! $C$3:$N$3)="2d Back (c+b)",'2d Back (c+b)'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,pla n!$C$3:$N$3)="2d Back str",'2d Back str'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan! $C$3:$N$3)="2d Back str pwr",'2d Back str pwr'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan! $C$3:$N$3)="2d HBR str pwr",'2d HBR str pwr'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan! $C$3:$N$3)="2d locks str",'2d Locks str'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan! $C$3:$N$3)="2d props str ecc",'2d Props str ecc'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan! $C$3:$N$3)="complex",'Complex Temp'!A1:C10,"")))))))))) Excel always seems to report an error on the 7th TODAY() function. When the formula is shorter there is no error. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
today() bug
I'm not completely sure, but I believe this would be because Excel is limitd
to a maximum of (I think) 7 nested IF statements per formula. "SaintJ" wrote in message ... I have made a formula in which today() is used multiple times and it returnsa with an error for no apparent reason? how can i overcome this one? formula- IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan!$C$3:$N$3)=" 3d Backs",' 3d Backs'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,pla n!$C$3:$N$3)="3d Locks",'3d Locks'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,pla n!$C$3:$N$3)="3d Props",'3d Props'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,pla n!$C$3:$N$3)="3d HBR",'3d HBR'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan! $C$3:$N$3)="2d Back (c+b)",'2d Back (c+b)'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,pla n!$C$3:$N$3)="2d Back str",'2d Back str'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan! $C$3:$N$3)="2d Back str pwr",'2d Back str pwr'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan! $C$3:$N$3)="2d HBR str pwr",'2d HBR str pwr'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan! $C$3:$N$3)="2d locks str",'2d Locks str'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan! $C$3:$N$3)="2d props str ecc",'2d Props str ecc'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan! $C$3:$N$3)="complex",'Complex Temp'!A1:C10,"")))))))))) Excel always seems to report an error on the 7th TODAY() function. When the formula is shorter there is no error. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
today() bug
It would return an error it Today is not found in you lookup range.
You have nested if statements exceeding 7 levels. (which is the limit) In your first range, you have a space at the start of the sheet name. There is no reason to return a multicell range unless this is going to be a multicell array formulas. You can use the must simpler formula =IF(iserror(LOOKUP(TODAY(),plan!$C$2:$N$2,plan!$C$ 3:$N$3)),"Not Found",Indirect("'" & LOOKUP(TODAY(),plan!$C$2:$N$2,plan!$C$3:$N$3) & "'!A1")) if you change any cell in row3 from Complex to Complex Temp to match the sheet name. -- Regards, Tom Ogilvy "SaintJ" wrote in message ... I have made a formula in which today() is used multiple times and it returnsa with an error for no apparent reason? how can i overcome this one? formula- IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan!$C$3:$N$3)=" 3d Backs",' 3d Backs'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,pla n!$C$3:$N$3)="3d Locks",'3d Locks'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,pla n!$C$3:$N$3)="3d Props",'3d Props'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,pla n!$C$3:$N$3)="3d HBR",'3d HBR'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan! $C$3:$N$3)="2d Back (c+b)",'2d Back (c+b)'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,pla n!$C$3:$N$3)="2d Back str",'2d Back str'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan! $C$3:$N$3)="2d Back str pwr",'2d Back str pwr'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan! $C$3:$N$3)="2d HBR str pwr",'2d HBR str pwr'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan! $C$3:$N$3)="2d locks str",'2d Locks str'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan! $C$3:$N$3)="2d props str ecc",'2d Props str ecc'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan! $C$3:$N$3)="complex",'Comp lex Temp'!A1:C10,"")))))))))) Excel always seems to report an error on the 7th TODAY() function. When the formula is shorter there is no error. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
today() bug
Thanks, I thought this may have been it. Is there any way of fitting more
formulas in? I am trying to create a workbook in which variables can be planned over a course of time for a number of subjects from a selection in a drop down list. On the current week a worksheet will be copied into another worksheet corresponding to the subject to be printed. I have been using a formula using conditional formulas one after the other. So, if it is not 'variable 1' then is it 'variable 2' if not, then is it 'variable 3' and so on. Is there a quicker way of doing this? Is there perhaps a way of refering to the same cell (which has been searched for previously in the formula) over and over without using the lookup function over again? "PCLIVE" wrote: I'm not completely sure, but I believe this would be because Excel is limitd to a maximum of (I think) 7 nested IF statements per formula. "SaintJ" wrote in message ... I have made a formula in which today() is used multiple times and it returnsa with an error for no apparent reason? how can i overcome this one? formula- IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan!$C$3:$N$3)=" 3d Backs",' 3d Backs'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,pla n!$C$3:$N$3)="3d Locks",'3d Locks'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,pla n!$C$3:$N$3)="3d Props",'3d Props'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,pla n!$C$3:$N$3)="3d HBR",'3d HBR'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan! $C$3:$N$3)="2d Back (c+b)",'2d Back (c+b)'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,pla n!$C$3:$N$3)="2d Back str",'2d Back str'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan! $C$3:$N$3)="2d Back str pwr",'2d Back str pwr'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan! $C$3:$N$3)="2d HBR str pwr",'2d HBR str pwr'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan! $C$3:$N$3)="2d locks str",'2d Locks str'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan! $C$3:$N$3)="2d props str ecc",'2d Props str ecc'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan! $C$3:$N$3)="complex",'Complex Temp'!A1:C10,"")))))))))) Excel always seems to report an error on the 7th TODAY() function. When the formula is shorter there is no error. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
today() bug
On Mon, 10 Oct 2005 06:29:02 -0700, SaintJ
wrote: I have made a formula in which today() is used multiple times and it returnsa with an error for no apparent reason? how can i overcome this one? formula- IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan!$C$3:$N$3)= "3d Backs",' 3d Backs'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,pl an!$C$3:$N$3)="3d Locks",'3d Locks'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,pl an!$C$3:$N$3)="3d Props",'3d Props'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,pl an!$C$3:$N$3)="3d HBR",'3d HBR'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan !$C$3:$N$3)="2d Back (c+b)",'2d Back (c+b)'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,pl an!$C$3:$N$3)="2d Back str",'2d Back str'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan !$C$3:$N$3)="2d Back str pwr",'2d Back str pwr'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan !$C$3:$N$3)="2d HBR str pwr",'2d HBR str pwr'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan !$C$3:$N$3)="2d locks str",'2d Locks str'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan !$C$3:$N$3)="2d props str ecc",'2d Props str ecc'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan !$C$3:$N$3)="complex",'Complex Temp'!A1:C10,"")))))))))) Excel always seems to report an error on the 7th TODAY() function. When the formula is shorter there is no error. Excel has a nesting limit of seven functions. --ron |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
today() bug
See Tom's reply. This may be close to what you want.
Best Regards, Paul "SaintJ" wrote in message ... Thanks, I thought this may have been it. Is there any way of fitting more formulas in? I am trying to create a workbook in which variables can be planned over a course of time for a number of subjects from a selection in a drop down list. On the current week a worksheet will be copied into another worksheet corresponding to the subject to be printed. I have been using a formula using conditional formulas one after the other. So, if it is not 'variable 1' then is it 'variable 2' if not, then is it 'variable 3' and so on. Is there a quicker way of doing this? Is there perhaps a way of refering to the same cell (which has been searched for previously in the formula) over and over without using the lookup function over again? "PCLIVE" wrote: I'm not completely sure, but I believe this would be because Excel is limitd to a maximum of (I think) 7 nested IF statements per formula. "SaintJ" wrote in message ... I have made a formula in which today() is used multiple times and it returnsa with an error for no apparent reason? how can i overcome this one? formula- IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan!$C$3:$N$3)=" 3d Backs",' 3d Backs'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,pla n!$C$3:$N$3)="3d Locks",'3d Locks'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,pla n!$C$3:$N$3)="3d Props",'3d Props'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,pla n!$C$3:$N$3)="3d HBR",'3d HBR'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan! $C$3:$N$3)="2d Back (c+b)",'2d Back (c+b)'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,pla n!$C$3:$N$3)="2d Back str",'2d Back str'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan! $C$3:$N$3)="2d Back str pwr",'2d Back str pwr'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan! $C$3:$N$3)="2d HBR str pwr",'2d HBR str pwr'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan! $C$3:$N$3)="2d locks str",'2d Locks str'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan! $C$3:$N$3)="2d props str ecc",'2d Props str ecc'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan! $C$3:$N$3)="complex",'Complex Temp'!A1:C10,"")))))))))) Excel always seems to report an error on the 7th TODAY() function. When the formula is shorter there is no error. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| if today = then | New Users to Excel | |||
| =TODAY() | Excel Worksheet Functions | |||
| IF TODAY equals date in cell A10, or if TODAY is beyond that date | Excel Worksheet Functions | |||
|
=IF(OR(TODAY() |
Excel Discussion (Misc queries) | |||
| Today() | Excel Programming | |||
Linear Mode
