Formula too Long Error

[moneytastesbad]

I have a sheet that refrences cells in another workbook. Due to the long name of the other workbook, I am running into an Formula Too Long Error.

I see on Excel Help that formulas are limited to 1,024 characters.

Is there any kind of workaround for this other than renaming the file?

 

[erik.van.geit]

Hi,

perhaps you can shorten the formula itself ?
example: replace nested IFs by a VLOOKUP

or cut it into parts
example of cutting to several cells
=IF(AND(A1 = "yes",B1 = "no"), "OK", "not OK")
you could put
=AND(A1 = "yes",B1 = "no") in C1
and shorten the formula to
=IF(C1, "OK", "not OK")

else INDIRECT might be an option
example
http://www.mrexcel.com/board2/viewtopic.php?t=238871&highlight=indirect

kind regards,
Erik

 

[moneytastesbad]

Here is the formula that I am working with....sorta.

SUMPRODUCT(--(LEFT(OrigDatCopy!G4:G352,5)="SAPMR"),--(OrigDatCopy!J4:J352="EXP"),--(RIGHT(OrigDatCopy!$H$4:$H$352,3)<>"INT"),((OrigDatCopy!P2>TODAY())*(OrigDatCopy!P4:P352))+((OrigDatCopy!V2>TODAY())*(OrigDatCopy!V4:V352))+((OrigDatCopy!AB2>TODAY())*(OrigDatCopy!AB4:AB352))+((OrigDatCopy!AH2>TODAY())*(OrigDatCopy!AH4:AH352))+((OrigDatCopy!AN2>TODAY())*(OrigDatCopy!AN4:AN352))+((OrigDatCopy!AT2>TODAY())*(OrigDatCopy!AT4:AT352))+((OrigDatCopy!AZ2>TODAY())*(OrigDatCopy!AZ4:AZ352))+((OrigDatCopy!BF2>TODAY())*(OrigDatCopy!BF4:BF352))+((OrigDatCopy!BL2>TODAY())*(OrigDatCopy!BL4:BL352))+((OrigDatCopy!BR2>TODAY())*(OrigDatCopy!BR4:BR352))+((OrigDatCopy!BX2>TODAY())*(OrigDatCopy!BX4:BX352))+((OrigDatCopy!CD2>TODAY())*(OrigDatCopy!CD4:CD352))+((OrigDatCopy!CJ2>TODAY())*(OrigDatCopy!CJ4:CJ352)))

This formula is what I am using to solve a problem I posted about here
http://www.mrexcel.com/board2/viewtopic.php?t=238803&highlight=


This is actualy slightly different than the one I am working on now, but it is basicly the same. The difference is it is refering to a sheet in a workbook called: FY07 Corporate IT Project Summary August Baseline (version 1).xls] so that workbook title needs to be added before each cell reference. I also need to add another critera for it to sort by.


Does this make any sense?

Do you have any suggestions for the original problem that dose not require such a long formula?

 

[erik.van.geit]

Do you have any suggestions for the original problem that dose not require such a long formula?

cut it into parts

other suggestions
shorten the sheetname
put formula in original workbook and refer to that cell

INDIRECT looks too complicated to me in this case

 

[jmckeone]

have you considered using named ranges? Simplifies reading the formula as well as shortening its overall length.

 

[Peter_SSs]

moneytastesbad

This is not aimed directly at solving your problem (though it would shorten the formula marginally), but rather at making formulas more efficient.

I note that you have used the TODAY() function many times in this formula. This function is 'volatile' and its repeated use in a sheet can slow the calculation of the sheet noticeably. A suggestion is to put the formula =TODAY() in an unused (and possibly hidden) cell (say IV1) and then refer to that cell in your formulas. So your formula would start off like this:
=SUMPRODUCT(--(LEFT(OrigDatCopy!G4:G352,5)="SAPMR"),--(OrigDatCopy!J4:J352="EXP"),--(RIGHT(OrigDatCopy!$H$4:$H$352,3)<>"INT"),((OrigDatCopy!P2>IV1)*(OrigDatCopy!P4:P352))+((OrigDatCopy!V2>IV1)*(OrigDatCopy!V4:V352))+((OrigDatCopy!AB2>IV1)*...