How to overcome the "Formula too long" error on windows excel...

[puppykak]

I have worked on this really difficult long formula, but the problem is, I have been working from my Mac. And my office, which this formula was made for, is all PC. Now generally this isn't a problem, but apparently PC's have a limit on how long the formula can be. Is there anyway to overcome this? I really need this formula to work..

Please help!

 

[wsjackman]

what is the formula?
what is the length of the formula?

 

[puppykak]

=
IF(B3="Company 1", VLOOKUP(B15,'[Outside Excel Document.xls]Sheet1'!$A$8:$I$11,2,FALSE),"")&
IF(B3="Company 2", VLOOKUP(B15,'[Outside Excel Document.xls]Sheet1'!$A$8:$I$11,3,FALSE),"")&
IF(B3="Company 3", VLOOKUP(B15,'[Outside Excel Document.xls]Sheet1'!$A$8:$I$11,4,FALSE),"")&
IF(B3="Company 4", VLOOKUP(B15,'[Outside Excel Document.xls]Sheet1'!$A$8:$I$11,5,FALSE),"")&
IF(B3="Company 5", VLOOKUP(B15,'[Outside Excel Document.xls]Sheet1'!$A$8:$I$11,6,FALSE),"")&
IF(B3="Company 6", VLOOKUP(B15,'[Outside Excel Document.xls]Sheet1'!$A$8:$I$11,7,FALSE),"")&
IF(B3="Company 7", VLOOKUP(B15,'[Outside Excel Document.xls]Sheet1'!$A$8:$I$11,8,FALSE),"")&
IF(B3="Company 8", VLOOKUP(B15,'[Outside Excel Document.xls]Sheet1'!$A$8:$I$11,9,FALSE),"")

 

[puppykak]

...the same one we've been working on...

the issue is, when the outside excel document is entered in the actual one, it has to trace the document to where it lives in the server. So it gets really long....

 

[puppykak]

what is the formula?
what is the length of the formula?

1, 026 characters

spaces don't count right?

 

[lenze]

Have you considered using Index_Match? Might need to reargae you data some, but surely it's better. BTW, I'm not familar with Excel on the Mac, but I don't know what the &s are for in your formula. My guess is you are not concatenating. What does your data table look like.

lenze

 

[puppykak]

this is the outside document:

 

[wsjackman]

actually he is concatinating, see
http://www.mrexcel.com/forum/showthread.php?p=1993550#post1993550

puppykak
I think that now you have the formula working, you should go back and look at some of the match() column selector suggestions. this will reduce the size dramatically.

 

[puppykak]

actually he is concatinating, see
http://www.mrexcel.com/forum/showthread.php?p=1993550#post1993550

puppykak
I think that now you have the formula working, you should go back and look at some of the match() column selector suggestions. this will reduce the size dramatically.

I still don't understand how those work, do you think you could show me how they would work with my formula?

 

[puppykak]

again, leaving work...won't be back till Monday, but I will check back here this weekend at home. thank you.