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!

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!

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!

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!

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)".