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!

 

[lenze]

I believe from your example, this might work

=INDEX('[Outside Excel Document.xls]Sheet1'!B5:I7,MATCH(B15,'[Outside Excel Document.xls]Sheet1'!A5:A7,0),MATCH(B3,'[Outside Excel Document.xls]Sheet1'!B4:I4,0))

Never tried across WorkBooks, but it might work.
lenze

 

[puppykak]

it just came up with #N/A

 

[puppykak]

I am back!!!

 

[Giordano Bruno]

No chance of changing the file name "Outside Excel Document" to "OED"?

 

[puppykak]

The thing is, it has to follow the document address to find where it lives through the computer. So the address is the long part. Also this is a company document, and others need to know what it is called and be able to easily identify whats in it. I cannot change the name...

 

[wsjackman]

if you use "Company 1" etc in your working spread sheet

=VLOOKUP(B15,'[Outside Excel Document.xls]Sheet1'!$A$8:$I$11,value(right(b33,1)),FALSE)
should work

if you don't

make a table
companys

Company 1 2
Company 2 3

etc

=VLOOKUP(B15,'[Outside Excel Document.xls]Sheet1'!$A$8:$I$11,Vlookup(b3,companies,2,0),)

should work

the formulas I put in initially came through garbled, hopefully I caught them in time

 

[wsjackman]

the second formula is sill being garbled

=VLOOKUP(B15,'[Outside Excel Document.xls]Sheet1'!$A$8:$I$11,Vlookup(b3,companies,2,0),0)

is what I am trying to use for the second formula

 

[puppykak]

I don't understand, can you please explain what you did? With the table of companies too...

 

[wsjackman]

if e10 through f20 is empty
otherwise use any empty block

write the actual name of company one in E10
the actual name of company 2 in E11

etc through the actual name of company 9 in E18

in f10 enter 2
in f11 enter 3

etc.

You can either name the range E10:F20 as companies or just use the range in the vlookup



=VLOOKUP(B15,'[Outside Excel Document.xls]Sheet1'!$A$8:$I$11,Vlookup(b3,$E1$0:$f$20,2,0),0)</PRE>

 

[puppykak]

ohh I see. But I can't do that. I can't have the table in the working table. I need it to be just in the outside document. Because if someone changes the outside document, I need it to be able to update the working table. I can't have it so that they would need to change the outside document and the working table.