Formula which includes new sheets too?

[Database?'s]

Hi all,

I don't know if this is possible, but any suggestions would be much
appreciated!

I have a spreadsheet with a few sheets, 1 for each site and then a Summary
sheet which displays totals.

eg. Site 1 shows how much has been fee'd for that site in each month, and
the Summary Sheet shows how much has been fee'd for all sites in that month,
by using a formula which takes the value's from each sheet.

Is there a way of editing the formula so it will automatically include cell
H3 for any sheet, including any new sheets added, without me having to
amend the formulas?

Thanks,

Kirstie

 

[Max]

Maybe just use a simple sandwich
Insert 2 new blank sheets named: First, Last

Move all sheets to be summed within the above 2 sheets

Then in your summary/consol sheet
you could use in say, B2: =SUM(First:Last!H3)
and copy B2 across/fill down as appropriate

Any new sheets to be added, just move it within First, Last
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,400, Files:361, Subscribers:58
xdemechanik

 

[Database?'s]

Thanks all, this works perfectly!

Kirstie

If you have a formula like:

=SUM(first:last!H3)

where "first" and "last" are sheet names, then Excel will add all the
values from cells H3 in all sheets between (and including) first and
last. So, if you add a new sheet, all you need to do is ensure that
its tab is positioned between first and last and it will be included
in the formula. Obviously, the tab for your summary sheet has to be
positioned outside the "sandwich" formed by first and last.

Hope this helps.

Pete

 

[Pete_UK]

You're welcome, Kirstie - thanks for feeding back.

Pete