excel formula too long to be displayed

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...
 
H

Harlan Grove

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?

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top