I am a novice to excel and this one is driving me nuts. I am trying to add
the values of a cell across worksheets. I can add the values for all the
worksheets with the following formula:
=SUM(End:Begin!F24) where End is the last sheet and Begin is the first. One
sheet is added daily between the two, and named for the date, ie:03-08-09.
I would like to be able to set up a formula that would add (Sum) the value
of a cell in the current worksheet and the same cells in all of the preceding
worksheets.
Of course I could manually insert the name of the worksheet in the formula
of each new worksheet everyday, ie: =SUM('02-24-09:Begin'!F24), but I would
like to know if there is a way to write it so that the name of the CURRENT
worksheet is populated automatically in the formula. I do have A1 set to
pull the name of the worksheet, ie:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,34).

Thanks!

I don't understand what you're trying to do.

If you have this formula:

=SUM(End:Begin!F24)

And you add a new sheet each day and place it between Begin and End the new
sheet will be included in the calculation.

--
Biff
Microsoft Excel MVP


"NHPilot43" wrote in message
...
I am a novice to excel and this one is driving me nuts. I am trying to add
the values of a cell across worksheets. I can add the values for all the
worksheets with the following formula:
=SUM(End:Begin!F24) where End is the last sheet and Begin is the first.
One
sheet is added daily between the two, and named for the date, ie:03-08-09.
I would like to be able to set up a formula that would add (Sum) the value
of a cell in the current worksheet and the same cells in all of the
preceding
worksheets.
Of course I could manually insert the name of the worksheet in the formula
of each new worksheet everyday, ie: =SUM('02-24-09:Begin'!F24), but I
would
like to know if there is a way to write it so that the name of the CURRENT
worksheet is populated automatically in the formula. I do have A1 set to
pull the name of the worksheet, ie:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,34).

Thanks!

Yes it does.

However, it calculates the total for ALL the sheets.

What I want to do is have a running total on each sheet that calculates from
the CURRENT worksheet (and not the ones AFTER it) to the first worksheet.

Like I said, I could enter the formula on each sheet, ie:
=SUM('02-24-09:Begin'!F24), and change the formula each day to match the name
of the worksheet, but I would like the formula to be dynamic and populate the
name of the current worksheet automatically into the formula.

"T. Valko" wrote:

I don't understand what you're trying to do.

If you have this formula:

=SUM(End:Begin!F24)

And you add a new sheet each day and place it between Begin and End the new
sheet will be included in the calculation.

--
Biff
Microsoft Excel MVP


"NHPilot43" wrote in message
...
I am a novice to excel and this one is driving me nuts. I am trying to add
the values of a cell across worksheets. I can add the values for all the
worksheets with the following formula:
=SUM(End:Begin!F24) where End is the last sheet and Begin is the first.
One
sheet is added daily between the two, and named for the date, ie:03-08-09.
I would like to be able to set up a formula that would add (Sum) the value
of a cell in the current worksheet and the same cells in all of the
preceding
worksheets.
Of course I could manually insert the name of the worksheet in the formula
of each new worksheet everyday, ie: =SUM('02-24-09:Begin'!F24), but I
would
like to know if there is a way to write it so that the name of the CURRENT
worksheet is populated automatically in the formula. I do have A1 set to
pull the name of the worksheet, ie:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,34).

Thanks!

If you're willing to use a User Defined Function.......

Function PrevSheet(rg As Range)
n = Application.Caller.Parent.Index
If n = 1 Then
PrevSheet = CVErr(xlErrRef)
ElseIf TypeName(Sheets(n - 1)) = "Chart" Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = Sheets(n - 1).Range(rg.Address).Value
End If
End Function

Example of use......................

Say you have 12 sheets, sheet1 through sheet12...........sheet names don't
matter.

In sheet1 you have a formula in A10 =SUM(A1:A9)

Select second sheet and SHIFT + Click last sheet

In active sheet A10 enter =SUM(PrevSheet(A10),A1:A9)

Ungroup the sheets.

Each A10 will have the sum of the previous sheet's A10 plus the sum of the
current sheet's A1:A9


Gord Dibben MS Excel MVP

On Mon, 9 Mar 2009 03:41:00 -0700, NHPilot43
wrote:

Yes it does.

However, it calculates the total for ALL the sheets.

What I want to do is have a running total on each sheet that calculates from
the CURRENT worksheet (and not the ones AFTER it) to the first worksheet.

Like I said, I could enter the formula on each sheet, ie:
=SUM('02-24-09:Begin'!F24), and change the formula each day to match the name
of the worksheet, but I would like the formula to be dynamic and populate the
name of the current worksheet automatically into the formula.

"T. Valko" wrote:

I don't understand what you're trying to do.

If you have this formula:

=SUM(End:Begin!F24)

And you add a new sheet each day and place it between Begin and End the new
sheet will be included in the calculation.

--
Biff
Microsoft Excel MVP


"NHPilot43" wrote in message
...
I am a novice to excel and this one is driving me nuts. I am trying to add
the values of a cell across worksheets. I can add the values for all the
worksheets with the following formula:
=SUM(End:Begin!F24) where End is the last sheet and Begin is the first.
One
sheet is added daily between the two, and named for the date, ie:03-08-09.
I would like to be able to set up a formula that would add (Sum) the value
of a cell in the current worksheet and the same cells in all of the
preceding
worksheets.
Of course I could manually insert the name of the worksheet in the formula
of each new worksheet everyday, ie: =SUM('02-24-09:Begin'!F24), but I
would
like to know if there is a way to write it so that the name of the CURRENT
worksheet is populated automatically in the formula. I do have A1 set to
pull the name of the worksheet, ie:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,34).

Thanks!

This won't dynamically update the values in all the sheets if a value in one
sheet (Sell A10 in this instance) is changed.

"Gord Dibben" wrote:

If you're willing to use a User Defined Function.......

Function PrevSheet(rg As Range)
n = Application.Caller.Parent.Index
If n = 1 Then
PrevSheet = CVErr(xlErrRef)
ElseIf TypeName(Sheets(n - 1)) = "Chart" Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = Sheets(n - 1).Range(rg.Address).Value
End If
End Function

Example of use......................

Say you have 12 sheets, sheet1 through sheet12...........sheet names don't
matter.

In sheet1 you have a formula in A10 =SUM(A1:A9)

Select second sheet and SHIFT + Click last sheet

In active sheet A10 enter =SUM(PrevSheet(A10),A1:A9)

Ungroup the sheets.

Each A10 will have the sum of the previous sheet's A10 plus the sum of the
current sheet's A1:A9


Gord Dibben MS Excel MVP

On Mon, 9 Mar 2009 03:41:00 -0700, NHPilot43
wrote:

Yes it does.

However, it calculates the total for ALL the sheets.

What I want to do is have a running total on each sheet that calculates from
the CURRENT worksheet (and not the ones AFTER it) to the first worksheet.

Like I said, I could enter the formula on each sheet, ie:
=SUM('02-24-09:Begin'!F24), and change the formula each day to match the name
of the worksheet, but I would like the formula to be dynamic and populate the
name of the current worksheet automatically into the formula.

"T. Valko" wrote:

I don't understand what you're trying to do.

If you have this formula:

=SUM(End:Begin!F24)

And you add a new sheet each day and place it between Begin and End the new
sheet will be included in the calculation.

--
Biff
Microsoft Excel MVP


"NHPilot43" wrote in message
...
I am a novice to excel and this one is driving me nuts. I am trying to add
the values of a cell across worksheets. I can add the values for all the
worksheets with the following formula:
=SUM(End:Begin!F24) where End is the last sheet and Begin is the first.
One
sheet is added daily between the two, and named for the date, ie:03-08-09.
I would like to be able to set up a formula that would add (Sum) the value
of a cell in the current worksheet and the same cells in all of the
preceding
worksheets.
Of course I could manually insert the name of the worksheet in the formula
of each new worksheet everyday, ie: =SUM('02-24-09:Begin'!F24), but I
would
like to know if there is a way to write it so that the name of the CURRENT
worksheet is populated automatically in the formula. I do have A1 set to
pull the name of the worksheet, ie:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,34).

Thanks!

Don't know what you mean by this.

Change a cell value on which sheet?


Gord Dibben MS Excel MVP

On Mon, 9 Mar 2009 09:21:02 -0700, NHPilot43
wrote:

This won't dynamically update the values in all the sheets if a value in one
sheet (Sell A10 in this instance) is changed.

"Gord Dibben" wrote:

If you're willing to use a User Defined Function.......

Function PrevSheet(rg As Range)
n = Application.Caller.Parent.Index
If n = 1 Then
PrevSheet = CVErr(xlErrRef)
ElseIf TypeName(Sheets(n - 1)) = "Chart" Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = Sheets(n - 1).Range(rg.Address).Value
End If
End Function

Example of use......................

Say you have 12 sheets, sheet1 through sheet12...........sheet names don't
matter.

In sheet1 you have a formula in A10 =SUM(A1:A9)

Select second sheet and SHIFT + Click last sheet

In active sheet A10 enter =SUM(PrevSheet(A10),A1:A9)

Ungroup the sheets.

Each A10 will have the sum of the previous sheet's A10 plus the sum of the
current sheet's A1:A9


Gord Dibben MS Excel MVP

On Mon, 9 Mar 2009 03:41:00 -0700, NHPilot43
wrote:

Yes it does.

However, it calculates the total for ALL the sheets.

What I want to do is have a running total on each sheet that calculates from
the CURRENT worksheet (and not the ones AFTER it) to the first worksheet.

Like I said, I could enter the formula on each sheet, ie:
=SUM('02-24-09:Begin'!F24), and change the formula each day to match the name
of the worksheet, but I would like the formula to be dynamic and populate the
name of the current worksheet automatically into the formula.

"T. Valko" wrote:

I don't understand what you're trying to do.

If you have this formula:

=SUM(End:Begin!F24)

And you add a new sheet each day and place it between Begin and End the new
sheet will be included in the calculation.

--
Biff
Microsoft Excel MVP


"NHPilot43" wrote in message
...
I am a novice to excel and this one is driving me nuts. I am trying to add
the values of a cell across worksheets. I can add the values for all the
worksheets with the following formula:
=SUM(End:Begin!F24) where End is the last sheet and Begin is the first.
One
sheet is added daily between the two, and named for the date, ie:03-08-09.
I would like to be able to set up a formula that would add (Sum) the value
of a cell in the current worksheet and the same cells in all of the
preceding
worksheets.
Of course I could manually insert the name of the worksheet in the formula
of each new worksheet everyday, ie: =SUM('02-24-09:Begin'!F24), but I
would
like to know if there is a way to write it so that the name of the CURRENT
worksheet is populated automatically in the formula. I do have A1 set to
pull the name of the worksheet, ie:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,34).

Thanks!