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.

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.

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.

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.

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

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.