=sum(first:last!"MyRange")

[jdmetcalfe]

Hi have mulitple worksheets all with a total cell named TotalVal i need a summery sheet that produces a grand total for all cells called TotalVal in the workbook. The TotalVal cell is not in the same place on each sheet and users need to be able to insert new sheets?

If the cell was static i could use =sum(first:last!A1) but i need to replace A1 with a name or use vba to do this?

Any help would be much apreciated

Thanks

James

[Richard Buttrey]

Hi,

Perhaps stating the obvious but by far the easiest solution is to make sure all total cells have the same cell address on each sheet. If you don;t want to disturb the layout of your many different sheets, why not add a new row 1 to each sheet and link A1 on each sheet to the total cell on that sheet, then you will have a consistent secondary total cell.

Failing that you will need VBA code, but that will need some logic to work and the code will need to know how to identify the total cell on each sheet.

[jdmetcalfe]

Hi thanks richard. Yes was thinking that would be a solution but just wanted to keep it tidy and thought their may be a solution with vba. Identifing the total cell is easy as it always has the defined name TotalVal but not sure of the vba code i would need to sum all values in all worksheets with the range TotalVal?

Thanks anyway

[Richard Buttrey]

Hi,

One way would be

Please Login or Register  to view this content.

where the dTotal variable is the value of all the TotalVal range names.

[jdmetcalfe]

Thanks that's great i'll give that a go.

[MrShorty]

One disadvantage to the VBA solution is that you will have to call the procedure each time you want it to update. If this is the only time you will ever want this sum to calculate then it is a moot point. If you are going to want the sum to recalculate when necessary, I think Richard's first option (put all the subtotals in the same cell, then sum the 3D range) is better for this, because then Excel can see how each subtotal fits into the overall sum, and can recalculate the sum during the calculation event when necessary. See http://office.microsoft.com/en-us/ex...010102346.aspx for discussion on 3D ranges in Excel.

The 2nd VBA option will work, but you will have to manually run the macro whenever you want the sum to calculate, or associate with the appropriate calculation and/or change event. It seems to me that you are creating a lot of "behind the scenes" untidiness to in an effort to avoid a little bit of visible untidiness moving/linking the subtotal cell to the same cell on each sheet.

Either option will work, just a few thoughts as you decide which approach to use.