Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count values once no matter how often they appear
I'm trying to sum data in Sheet1 from 50+ other sheets in the same file,
using =SUM(EUN1!AD8+EUN2!AD8+SV!AD8....and so on. However, I need to ammend the formula every time new sheet is added. Is there a formula which will automatically count the values in the respective cell in all sheets, irrespectively how many are added? Thanks a million! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count values once no matter how often they appear
Try the below..Sheet1 is the 1st sheet and Sheet3 is the last sheet...Try
inserting new sheets inbetween...Keep the first and last sheets names constant...and always make sure to insert in between =SUM(Sheet1:Sheet3!AD8) -- Jacob "musha-bsuha" wrote: I'm trying to sum data in Sheet1 from 50+ other sheets in the same file, using =SUM(EUN1!AD8+EUN2!AD8+SV!AD8....and so on. However, I need to ammend the formula every time new sheet is added. Is there a formula which will automatically count the values in the respective cell in all sheets, irrespectively how many are added? Thanks a million! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count values once no matter how often they appear
Create a sheet "sandwich".
Inset a new sheet to the immediate left of the first sheet you want to include in the calculation and name it something like Start. Inset a new sheet to the immediate right of the last sheet you want to include in the calculation and name it something like End. Then use a formula like this: =SUM(Start:End!AD8) When you create new sheets that you want to include in the calculation just insert them between the Start sheet and the End sheet. -- Biff Microsoft Excel MVP "musha-bsuha" wrote in message ... I'm trying to sum data in Sheet1 from 50+ other sheets in the same file, using =SUM(EUN1!AD8+EUN2!AD8+SV!AD8....and so on. However, I need to ammend the formula every time new sheet is added. Is there a formula which will automatically count the values in the respective cell in all sheets, irrespectively how many are added? Thanks a million! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count values once no matter how often they appear
T. Valko wrote:
Create a sheet "sandwich". Inset a new sheet to the immediate left of the first sheet you want to include in the calculation and name it something like Start. Inset a new sheet to the immediate right of the last sheet you want to include in the calculation and name it something like End. Then use a formula like this: =SUM(Start:End!AD8) When you create new sheets that you want to include in the calculation just insert them between the Start sheet and the End sheet. I like to use "All" for the Start sheet name and "Sheets" for the End sheet name. Then I hide them and you get a formula that looks like this: =SUM(All:Sheets!A1) As best I can tell, this will survive using "Move or Copy..." and selecting "(move to end)". |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count values once no matter how often they appear
One common way to address this is to add an empty sheet after Sheet1 and
before any of the sheets you want to sum. Name the new sheet FirstSht (for example). Add another empty sheet after the last sheet you want to sum. Name this new sheet LastSht. On Sheet1 use a SUM formula like =SUM(FirstSht:LastSht!AD8) FirstSht & LastSht don't affect the total because they are empty. If new sheets are added anywhere between FirstSht & LastSht, they will automatically be included in the SUM formula range. Hope this helps, Hutch "musha-bsuha" wrote: I'm trying to sum data in Sheet1 from 50+ other sheets in the same file, using =SUM(EUN1!AD8+EUN2!AD8+SV!AD8....and so on. However, I need to ammend the formula every time new sheet is added. Is there a formula which will automatically count the values in the respective cell in all sheets, irrespectively how many are added? Thanks a million! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date matter | New Users to Excel | |||
Every time I type 24-7 it changes to 24/Jul no matter what | Excel Discussion (Misc queries) | |||
Count Intervals of 2 Consecutive Values in same Row and Return Count across Row | Excel Worksheet Functions | |||
Count Intervals of 2 Numeric values in same Row and Return Count across Row | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions |