auto update sum range - for multiple sheets - indirect formula?

[mhapijnappel]

Hi,
I have a workbook with multiple sheets (company per sheet) and want a "total-sheet" of all companies/sheets in an identical format as the individual tabs.

I use a formula like: =SUM(Sheet1:Sheet2!D4) --> this formula is located in cell D4 of the "total-sheet", such that they have the same format as underlying sheets

But when I change the set-up of the sheets, for instance add a row in the underlying Sheets, let's say above row D, this formula in the total sheet no longer refers to the same row (or when I add a column to the same column, but that happens less) on the other sheets. So the total in former cell D4 will keep referring to row 4 on the other sheets (unlike a normal SUM(), formula)

Is there a way to make a formula relative to for instance row-nr and column-nr, such that the formulas in the total sheet don't need updating when a row is inserted? I could not manage that with an indirect formulas to replace "D4" in the above formula.

Thanks

[Richard Buttrey]

Perhaps a basic question but do you really need separate sheets for different companies?

You seem to be mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form/sheet layouts that they expect to see as the final report or which at first glance seems the best way of capturing data, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it.

You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality and of course Excel's standard filtering functionalit allows you te easily extrct subsets (e.g. a particular company) from the database.

So before you get too far with this I'd create a single sheet database that contains all the columns from your sheets and copy and paste all the sheet details underneath each other on a single sheet. Add an extra column to record the company reference for each row. If there are dozens of sheets a simple macro could do this for you so if you need help doing this then just ask.

Otherwise if there are only a few do the copy and paste manually.

[biex]

1. move the total formula to D1 on every sheet, maybe something like this =sum(D6:D100)
2. hide row 1
3. in Total sheet, =SUM(Sheet1:Sheet2!A1:N1)

This solve the problem if you ever add or delete row between D1 and D4
This solve the problem if you ever add any column between A to N... you may adjust the column what ever range you wish.

If this doesn't solve your problem, maybe you should show your worksheet and we look at it and see what we could do...