today() bug

[Guest]

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,plan!$C$3:$N$3)="3d Locks",'3d
Locks'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan!$C$3:$N$3)="3d Props",'3d
Props'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan!$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,plan!$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.

 

[PCLIVE]

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.

 

[Tom Ogilvy]

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

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,plan!$C$3:$N$3)="3d Locks",'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,plan!$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,""))))))))))

 

[Guest]

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?

 

[Ron Rosenfeld]

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,plan!$C$3:$N$3)="3d Locks",'3d
Locks'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan!$C$3:$N$3)="3d Props",'3d
Props'!A1:C10,IF(LOOKUP(TODAY(),plan!$C$2:$N$2,plan!$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,plan!$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

 

[PCLIVE]

See Tom's reply. This may be close to what you want.

Best Regards,
Paul