VARYING number of sheets in EXCEL
 

In Excel how can I sum the same cell on a VARYING number of sheets with
different names ? Is there a wildcard function or sum the same cell from ALL
worksheets function ?

Hi Darrin

the formula
=SUM(Sheet1:Sheet5!A1)
will sum cell A1 on Sheet 1 and Sheet 5 and any sheet between the two (in
tab order)

often what people do is put a blank sheet before and after the sheets they
want to sum - generally called something like "first" & "last" and then use
a formula like
=SUM(first:last!A5)
this means that more sheets can be added between these two without formulas
having to be re-written to include them.

Hope this helps
Cheers
JulieD

"Darrin" wrote in message
...
In Excel how can I sum the same cell on a VARYING number of sheets with
different names ? Is there a wildcard function or sum the same cell from
ALL
worksheets function ?

set up a dummy start and a dummy stop sheet and always have the other sheets
in between.

=sum(shart:stop!a1)

--
Don Guillett
SalesAid Software

"Darrin" wrote in message
...
In Excel how can I sum the same cell on a VARYING number of sheets with
different names ? Is there a wildcard function or sum the same cell from
ALL
worksheets function ?

Darrin,

In sum, you can specify a range of sheets, e.g.,

=sum(Sheet1:Sheet9)

This will sum all visible sheets from Sheet1 through Sheet9 as seen on the
screen. Any sheets that are non-contigous will not be summed. To ensure you
are summing all cells, you can use a function similar to the following:

function sumall(rng)
for each sht in activeworkbook.sheets
if application.thiscell.parent.name<sht.name then
total=total+sht.range(rng.address).value
end if
next
sumall=total

end function

http://HelpExcel.com

"Darrin" wrote:

In Excel how can I sum the same cell on a VARYING number of sheets with
different names ? Is there a wildcard function or sum the same cell from ALL
worksheets function ?