formula too long error?

[Guest]

I'm getting an error "formula too long" for the folloiwng IF statement:

=IF(B15="",0,IF(B19="Full Splashâ€, INDEX(‘Sheet 1’!$A$1:$L$3, MATCH
($B$5,’Sheet 1’!$A$1:$A$3,), MATCH($A$13,’Sheet
1’!$C$1:$L$1,))*VLOOKUP(B15,’Data Table’!$U$10:$V$15,2,FALSE), (INDEX(‘Sheet
1’!$A$6:$L$8, MATCH($B$5,’
Sheet 1’!$A$6:$A$8,),MATCH($A$13,’Sheet 1’!$C$6:$L$6,))*VLOOKUP(B15, ’Data
Table’!$U$10:$V$15,2,FALSE)))

I know this doesn't exceed the Nested If limit so I don't know what the
problem is.

Can I not use INDEX/MATCH/VLOOKUP within my IF statement?

 

[Guest]

I believe Excel formulae are limited to 255 characters and yours is > 300. It
has nothing to do with the use of INDEX, MATCH or LOOKUP.

 

[Guest]

Also could be my imagination but after counting brackets a couple times I
counted 11 left brackets and only 10 right brackets in the formula. For
every left there must be a right.

 

[Guest]

Do you know of a different way to do this so I don't go over the character
limit?
Thxs

 

[Peo Sjoblom]

Here is a method by using defined names

http://www.cpearson.com/excel/nested.htm

the example shows how to overcome the nesting limits but you can do the same
for long formulas as well by naming parts of them

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

 

[Guest]

Thank you!

Here is a method by using defined names

http://www.cpearson.com/excel/nested.htm

the example shows how to overcome the nesting limits but you can do the same
for long formulas as well by naming parts of them

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

 

[Gord Dibben]

Depending upon version of Excel, formula length can be 1024 characters.

2002 and newer allow the 1024 chars.

See help on specifications and limits.


Gord Dibben MS Excel MVP

 

[Guest]

I have Excel 2002, so why the "formula is too long" error? I guess it's an
erroneous error message.

 

[Dave Peterson]

1024 characters when measured in R1C1 reference style.

(for xl97+)

 

[Guest]

First, I count only 10 right parentheses and 11 left. Second, I'm finding
illegal characters:
1. The *double quote* following the text "Full Splash" isn't actually a
double quote but char 148 which looks almost identical.
2. The *single quotes* surrounding the sheet names (Sheet1 and Data Table)
are in fact char 146 instead which looks almost identical.

The above mentioned characters may look the same but are not accepted
worksheet formula syntax. Strange? Where did the formula come from?

Regards,
Greg