excel formula too long to be displayed

  • Thread starter Thread starter trista
  • Start date Start date
T

trista

I'm linking 26 workbooks to one parent workbook and so the
formula is very long ('H:\SALES\Budget\2005
\East\[CornCTStowMills.xls]CornCTStowMills'!
G52+'H:\SALES\Budget\2005\East\[CornGA.xls]CornGA'!
G52+'H:\SALES\Budget\2005\East\[Dekalb.xls]Dekalb'!G52+
and so on...)

My problem is that I'm receiving an error for one of my
formulas but my formula adding up the other workbooks'
cells is too long to display fully in the formula bar. Is
there a way to see the entire formula so I can edit?

Thanks...
 
trista said:
I'm linking 26 workbooks to one parent workbook and so the
formula is very long ('H:\SALES\Budget\2005
\East\[CornCTStowMills.xls]CornCTStowMills'!
G52+'H:\SALES\Budget\2005\East\[CornGA.xls]CornGA'!
G52+'H:\SALES\Budget\2005\East\[Dekalb.xls]Dekalb'!G52+
and so on...)

My problem is that I'm receiving an error for one of my
formulas but my formula adding up the other workbooks'
cells is too long to display fully in the formula bar. Is
there a way to see the entire formula so I can edit?
Click to expand...

If you opened all these workbooks, Excel would dispense with the full
directory path, so

'H:\SALES\Budget\2005\East\[CornCTStowMills.xls]CornCTStowMills'!G52

would become

[CornCTStowMills.xls]CornCTStowMills!G52

However, you may be better off using a different approach. Insert a new
worksheet in your summary workbook, and enter individual cell references to
these other workbooks in separate cells. If you name this new worksheet
Temp, you could do something like

Temp!A2:
='H:\SALES\Budget\2005\East\[CornCTStowMills.xls]CornCTStowMills'!G52

Temp!A3:
='H:\SALES\Budget\2005\East\[CornGA.xls]CornGA'!G52

Temp!A4:
='H:\SALES\Budget\2005\East\[Dekalb.xls]Dekalb'!G52

etc, then name the complete range something like SalesBudget2005East_G52 and
replace your existing formula with

=SUM(SalesBudget2005East_G52)

This won't add much to recalc time, it can add to file size (but not that
much if you apply no formatting in Temp), and it'd make tracking down errors
MUCH easier.
 
Back
Top