Formula length

[Guest]

Hello!,
I am consolidating data from about 35 Excel workbooks located on different
servers into a summary workbook. I have a macro to link a cell from all 35
sheets into one cell of summary book (This is sum of all cells being refred
with address).

This exceeds the formula length that Excel can support.

Can anybody suggest a workaround.

Thanks!
Abhay

 

[Guest]

Put a formula to sum half the number of sheets (or less depending on the
formula length) in one cell, the remainder in another then sum those two
cells.

 

[Bill Ridgeway]

You need a helper column(s). You need to import as much data into column1
then 2 then 3 etc. In column 4, if it is numerical data you need to sum the
previous 3 columns. If the data is text you need something like -
=IF(AND(A1="",B1=""),C1,IF(AND(A1="",C1=""),B1,IF(AND(B1="",C1=""),A1,"ERR")))

Regards.

Bill Ridgeway
Computer Solutions

 

[Guest]

Abhay,

I could be completely wrong here.

However, I ran into a similar situation awhile back. I opened all of the
referenced workbooks while working on the formula (which shortens the formula
because the references to the sheets are direct, not full path). After
closing the workbooks, the entire formula would no longer display, and if I
tried to edit the formula, I would get the error, formula too long. However,
the formulas continued to function properly.

HTH