Formula is too long

[Anton]

Version: 2004
Operating System: Mac OS X 10.4 (Tiger)

I have set up 20 worksheets for individual tenants and have linked the individual sheets into a summary worksheet. But I get the message "Formula is too long", so I am only able to sum up 16 of the tenants.

What do I do?

 

[macropod]

Hi Anton,

Without seeing the formula, it's hard to say. Using Named ranges is one possibility for reducing formula length. Also, depending on
how your workbook is laid out, you may be able to use a 3D formula to encompass all of the sheets, instead of referring to each
sheet individually.

Cheers

 

[Bob Greenblatt]

Version: 2004
Operating System: Mac OS X 10.4 (Tiger)

I have set up 20 worksheets for individual tenants and have linked the
individual sheets into a summary worksheet. But I get the message "Formula is
too long", so I am only able to sum up 16 of the tenants.

What do I do?

In addition to the other response, you might try data-consolidate.

 

[Anton]

Hi Bob

Thanks for your respond

I am sorry that I was a little inaccurate in my question. I have set up 20 individual WORKBOOKS. They are all identical, but I am not able to sum up all the workbooks (with help of links) because the formula is to long.

The links are about like this
=SUM('MacintoshHD:Reports:[Budget.xls]Annual'!$C$10:$+SUM('Macintosh HD:Reports:[Budget2.xls]Annual'!$C$10:$+.....etc.

Any suggestions on how to reduce formula length?

 

[macropod]

Hi Anton,

One way of working around this is to have an intermediate worksheet with cells that each link to the data for only one of the source
workbooks. If you put the cells with the corresponding source data all in the same column, on consecutive rows, your summation
formula then becomes nothing more than a summation of the values in the column on the intermediate worksheet. Plus, if you need to
add/delete links, you can simply add/delete a row on the intermediate worksheet.

Cheers