Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula length
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula length
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. "Abhay" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula length
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 "Abhay" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula length
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 "Abhay" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can a formula change a cells colour after a given length of time? | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
formula to standarize cell length | Excel Worksheet Functions | |||
maximum formula length | Excel Worksheet Functions |