Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Darrin
 
Posts: n/a
Default 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 ?
  #2   Report Post  
JulieD
 
Posts: n/a
Default

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 ?



  #3   Report Post  
Don Guillett
 
Posts: n/a
Default

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 ?



  #4   Report Post  
galimi
 
Posts: n/a
Default

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 ?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to make one CSV file from two Excel sheets? Marek L. Excel Discussion (Misc queries) 3 February 13th 05 08:47 PM
Create New Microsoft Excel Worksheet Has 3 Sheets Bassam Setting up and Configuration of Excel 0 February 9th 05 09:51 PM
Automatically number multiple sheets itguyintrainin Excel Discussion (Misc queries) 4 February 4th 05 12:49 AM
How do I print all sheets of an Excel workbook from explorer? Dave Walker Excel Discussion (Misc queries) 1 January 28th 05 10:19 PM
need an advice in excel sheets.... | m o [] Excel Discussion (Misc queries) 0 November 27th 04 11:36 PM


All times are GMT +1. The time now is 02:12 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"