Inerting name of current worksheet into fuction

[NHPilot43]

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!

 

[T. Valko]

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.

 

[NHPilot43]

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.

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

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!

 

[Gord Dibben]

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

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.

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

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!

 

[NHPilot43]

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

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

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.

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


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!

 

[Gord Dibben]

Don't know what you mean by this.

Change a cell value on which sheet?


Gord Dibben MS Excel MVP

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

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

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.

:

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


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!

 

[NHPilot43]

if (using your example) the value of a1:a9 were to be changed on any
previous sheet, the value of a10 on the current sheet would not be updated,
because the UDF is not dynamic.

I was hoping that since I have the name of the current sheet inserted into
A1, I could perhaps use INDIRECT and pull the name of the worksheet out of
A1, but I can't seem to get it to work.

Don't know what you mean by this.

Change a cell value on which sheet?


Gord Dibben MS Excel MVP

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

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

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.

:

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


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!

 

[Gord Dibben]

You say "would not be updated".

You do not say "does not update".

Have you actually tried it or are you making assumptions.

Any change of value in A1:A9 on any previous sheet that results in a change
to the SUM formula in A10 of that sheet will be reflected in all sheets
after that sheet.

Do you have calculation set to automatic?


Gord

if (using your example) the value of a1:a9 were to be changed on any
previous sheet, the value of a10 on the current sheet would not be updated,
because the UDF is not dynamic.

I was hoping that since I have the name of the current sheet inserted into
A1, I could perhaps use INDIRECT and pull the name of the worksheet out of
A1, but I can't seem to get it to work.

Don't know what you mean by this.

Change a cell value on which sheet?


Gord Dibben MS Excel MVP

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

:

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

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.

:

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


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!

 

[NHPilot43]

Basically what I am trying to do is to create a formula that will do this:

=SUM(|The Value of Cell A1|:Begin!F24)

The value of cell a1 is the name of the current worksheet and Begin is the
name of the first work sheet. However I cannot get this to work.

Don't know what you mean by this.

Change a cell value on which sheet?


Gord Dibben MS Excel MVP

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

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

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.

:

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


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!

 

[NHPilot43]

Yes I tried it....and it doesn't update if values are changed on previous
workseets...and yes calculate is set to automatic...

Thanks for the replies...this thing is driving me nuts!...but I'm learning!

You say "would not be updated".

You do not say "does not update".

Have you actually tried it or are you making assumptions.

Any change of value in A1:A9 on any previous sheet that results in a change
to the SUM formula in A10 of that sheet will be reflected in all sheets
after that sheet.

Do you have calculation set to automatic?


Gord

if (using your example) the value of a1:a9 were to be changed on any
previous sheet, the value of a10 on the current sheet would not be updated,
because the UDF is not dynamic.

I was hoping that since I have the name of the current sheet inserted into
A1, I could perhaps use INDIRECT and pull the name of the worksheet out of
A1, but I can't seem to get it to work.

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

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

:

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

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.

:

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


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!

 

[NHPilot43]

GOT IT......I inserted Apllication.Volitile at the head of the UDF and it
works like a charm....

Your the man....Thanks!!!!

You say "would not be updated".

You do not say "does not update".

Have you actually tried it or are you making assumptions.

Any change of value in A1:A9 on any previous sheet that results in a change
to the SUM formula in A10 of that sheet will be reflected in all sheets
after that sheet.

Do you have calculation set to automatic?


Gord

if (using your example) the value of a1:a9 were to be changed on any
previous sheet, the value of a10 on the current sheet would not be updated,
because the UDF is not dynamic.

I was hoping that since I have the name of the current sheet inserted into
A1, I could perhaps use INDIRECT and pull the name of the worksheet out of
A1, but I can't seem to get it to work.

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

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

:

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

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.

:

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


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!

 

[Gord Dibben]

Good to hear

For the other solution with the sheetname in a cell, look at the INDIRECT
function


Gord

GOT IT......I inserted Apllication.Volitile at the head of the UDF and it
works like a charm....

Your the man....Thanks!!!!

You say "would not be updated".

You do not say "does not update".

Have you actually tried it or are you making assumptions.

Any change of value in A1:A9 on any previous sheet that results in a change
to the SUM formula in A10 of that sheet will be reflected in all sheets
after that sheet.

Do you have calculation set to automatic?


Gord

if (using your example) the value of a1:a9 were to be changed on any
previous sheet, the value of a10 on the current sheet would not be updated,
because the UDF is not dynamic.

I was hoping that since I have the name of the current sheet inserted into
A1, I could perhaps use INDIRECT and pull the name of the worksheet out of
A1, but I can't seem to get it to work.



:

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

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

:

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

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.

:

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


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!