Excel Formula too long

**Crown** · 11-17-2009, 06:10 PM

Hi, little help please...

I've created a workbook/timsheet for each of my 15 contractors to record their hours worked each day. Each of them are working on 53 different companies. Their timesheets have 55 tabs, 2 summary tabs that summarize 53tabs that lay out time spent each day on each company. I have a Master workbook that pulls data from each of the contractor timesheets to summarize it all.

When I try to update the sources (the contractor workbooks) from one month to the next I get an error message that the formula is too long. Below are a couple examples of what the formulas looks like in the Master workbook:

A) ='[RS Timesheet - 11.26.09.xls]SOW Summary'!B69+'[LG Timesheet - 11.26.09.xls]SOW Summary'!B69+'[SB Timesheet - 11.26.09.xls]SOW Summary'!B69+'[KB Timesheet - 11.26.09.xls]SOW Summary'!B68+'[AG Timesheet - 11.26.09.xls]SOW Summary'!B69+'[CC Timesheet - 11.26.09.xls]SOW Summary'!B68+'[TK Timesheet - 11.26.09.xls]SOW Summary'!B69+'[CK Timesheet - 11.26.09.xls]SOW Summary'!B69+'[RL Timesheet - 11.26.09.xls]SOW Summary'!B69+'[DS Timesheet - 11.26.09.xls]SOW Summary'!B69+'[SD Timesheet - 11.26.09.xls]SOW Summary'!B69+'[VA Timesheet - 11.26.09.xls]SOW Summary'!B69+'[MH Timesheet - 11.26.09.xls]SOW Summary'!B69+'[TP Timesheet - 11.26.09.xls]SOW Summary'!B69

B) ='[RS Timesheet - 11.26.09.xls]SOW Summary'!K9+'[LG Timesheet - 11.26.09.xls]SOW Summary'!K9+'[SB Timesheet - 11.26.09.xls]SOW Summary'!K9+'[KB Timesheet - 11.26.09.xls]SOW Summary'!K9+'[AG Timesheet - 11.26.09.xls]SOW Summary'!K9+'[CC Timesheet - 11.26.09.xls]SOW Summary'!K9+'[TK Timesheet - 11.26.09.xls]SOW Summary'!K9+'[CK Timesheet - 11.26.09.xls]SOW Summary'!K9+'[RL Timesheet - 11.26.09.xls]SOW Summary'!K9+'[DS Timesheet - 11.26.09.xls]SOW Summary'!K9+'[SD Timesheet - 11.26.09.xls]SOW Summary'!K9+'[VA Timesheet - 11.26.09.xls]SOW Summary'!K9+'[MH Timesheet - 11.26.09.xls]SOW Summary'!K9+'[TP Timesheet - 11.26.09.xls]SOW Summary'!K9

I'm not sure how I can simplify the formulas as they pull from separte workbooks/timsheets that already summarize the information. I'm also not sure if it is the formula in the contractor's timesheets that is adding to the problem. For example, in example B, '[RS Timesheet - 11.26.09.xls]SOW Summary'!K9 (the beginning of the forumula) pulls from a cell in the contractor's ('RS') worksheet that has a long formula as well, and it may be contributing to the "Formula too long" error message, or maybe not. The 'long formula' I'm referring to is as follows (it summarizes work done for each of the 53 companies):

='565 Peach'!C8+'Acts Col'!C8+Ameri!C8+ANBF!C8+ANBT!C8+BankF!C8+BBHB!C8+Biltmore!C8+Brook!C8+Clockt!C8+CCM!C8+CGM!C8+'CGM II'!C8+CRE!C8+Falcon!C8+'Glen. C'!C8+'Glen. Proj.'!C8+GPE!C8+GPSS!C8+Gold!C8+Gresham!C8+Hawthorne!C8+Humbolt!C8+'Intang. Asset'!C8+Intercept!C8+'Irwin FSB'!C8+'JS&L'!C8+Landm!C8+MSM!C8+Merced!C8+Meritage!C8+'MHB Fin.'!C8+'MHB HELP'!C8+'MHB Leas'!C8+'MHB Title'!C8+'MHB Novi'!C8+'MH Insur.'!C8+'MH Security'!C8+Netbank!C8+OHH!C8+'Omni App'!C8+'Omni Comm'!C8+'Omni Lease'!C8+'Omni Prop'!C8+Onyx!C8+'Ridge Road'!C8+'Salem Cap.'!C8+SRES!C8+'Shores of P.'!C8+SFG!C8+TeamBank!C8+Medici!C8+WSG!C8

I would think that since the Master Workbook pulls from a cell that summarizes the above formula, it wouldn't contribute to the problem. But I'm just putting it all out there. I'm guessing it is one of the two example formulas ( A & B).

is there a way to fix this, or do I just need a newer version of excel to manage this complex set of workbooks?

Sorry for the book-of-an-explanation. I appreciate any help you might have to offer.

Thanks,
Chase

**Richard Buttrey** · 11-17-2009, 06:21 PM

Hi,

Difficult to comment without seeing the problem in context, but intuitively I feel that the formula is becoming inefficient and too complex.

Here are a couple of ideas.

1. Rather than rely on Excel functions, get a macro to do it. This could automatically open each of the contributory workbooks, grab the data required and summarise it as it goes along, concluding with an instruction that would add the summary totals to your master workbook.

2. To sum cells across multi tabs in a single workbook provided each cell means the same thing in each tab, the standard solution is to add an additional sheet before the first sheet you want to summarise, and an additional sheet after the last sheet you want to summarise. Name these sheets 'Start' and 'End' and hide them.

Now on your summary sheet use formulas like the following.

Please Login or Register  to view this content.

This will sum all the cells in A1 in all the sheets that are placed between the Start/End sheets.

Hope this gives you a couple of ideas.

Regards

**broro183** · 11-17-2009, 06:38 PM

hi Chase,

Welcome to the Forum

The length of the formulae in the contractor's workbook won't add to the problem.

Yes, formula can be too long - here's a quote from the Excel 2002 Help files:

Length of formula contents 1,024 characters

.
Your examples currently have lengths of 658 & 644 respectively, but when the linked files are closed, the formulae expands to include the full file path which will then well & truly push you over the 1024 limit. AFAIK, these limits didn't change in Excel 2003.

To help over come this problem, you could:
- open all the linked files before opening the Master file
- shorten the folder path by storing them in a folder closer to the root of the Drive (probably not ideal)
- shorten/abbreviate sheet names as much as possible w/o them becoming meaningless.
- change the way the information is drawn into the master file eg use a macro that adds the values from specific cells in each of the linked files & either hardcodes the final value or shows a sum with hardcoded values for each file instead of the file name & reference. (as Richard says, this may be the way to go?)

hth
Rob

**SDruley** · 11-17-2009, 08:27 PM

In the future you may want to consider the power of pivot tables. It would appear that your application would be greatly simplified

**broro183** · 11-18-2009, 02:33 AM

Good point SDruley - a nuisance to set up initially but quite likely to be well worth the effort.

Chase, to add to the Pivot Table (PT) suggestion...
This could be done by redesigning the workbooks to use a database style layout of the information, where each row represents a set of information with the different columns providing specific detail. Then PT's could be set up to summarise the data as necessary within the individual workbooks & they can even be set up to pull the information into the Master file.
Here is a link to some further explanation & examples of PT's: http://peltiertech.com/Excel/Pivots/pivotstart.htm

hth
Rob