Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to make one CSV file from two Excel sheets? | Excel Discussion (Misc queries) | |||
Create New Microsoft Excel Worksheet Has 3 Sheets | Setting up and Configuration of Excel | |||
Automatically number multiple sheets | Excel Discussion (Misc queries) | |||
How do I print all sheets of an Excel workbook from explorer? | Excel Discussion (Misc queries) | |||
need an advice in excel sheets.... | Excel Discussion (Misc queries) |