I am trying to reference the current tab name in a formula. I am trying to
get a running total across several different sheets, but here is the catch,
those sheets have not been created yet. I have a template for a daily report
that a person will copy sheet over and over until project done. I want the
formula for the running total to reference the tab name so as copied it will
update this formula. Here is my formula =SUM(Day1:Day2!L20) Day1 and Day2
being the sheets, Day2 needs to be a general reference to current sheet
somehow but I can't figure it out

Before the first WORKSHEET ('tab' is the dohicky used to open a worksheet),
insert a blank worksheet called Alpha. After the last worksheet add a blank
worksheet called Omega.
Always insert new worksheet between the two
Use formula =SUM(Alpha:Omega!L20)
Of course, you can make up names that are more relevant to you
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"excelhurtsme" wrote in message
...
I am trying to reference the current tab name in a formula. I am trying to
get a running total across several different sheets, but here is the
catch,
those sheets have not been created yet. I have a template for a daily
report
that a person will copy sheet over and over until project done. I want the
formula for the running total to reference the tab name so as copied it
will
update this formula. Here is my formula =SUM(Day1:Day2!L20) Day1 and Day2
being the sheets, Day2 needs to be a general reference to current sheet
somehow but I can't figure it out

....and for neatness, consider HIDING the worksheets Alpha and Omega by right-
clicking on them and selecting "Hide".
Also, PROTECTING Alpha and Omega worksheets would add another level of
problem prevention. (You don't want any user to ever delete these apparently
unnecessary Alpha and Omega worksheets and you don't want anybody putting
values in them.)
One more thing: Put an explanation in these Alpha and Omega worksheets,
explaining their purpose and use.

"Bernard Liengme" wrote:

Before the first WORKSHEET ('tab' is the dohicky used to open a worksheet),
insert a blank worksheet called Alpha. After the last worksheet add a blank
worksheet called Omega.
Always insert new worksheet between the two
Use formula =SUM(Alpha:Omega!L20)
Of course, you can make up names that are more relevant to you
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"excelhurtsme" wrote in message
...
I am trying to reference the current tab name in a formula. I am trying to
get a running total across several different sheets, but here is the
catch,
those sheets have not been created yet. I have a template for a daily
report
that a person will copy sheet over and over until project done. I want the
formula for the running total to reference the tab name so as copied it
will
update this formula. Here is my formula =SUM(Day1:Day2!L20) Day1 and Day2
being the sheets, Day2 needs to be a general reference to current sheet
somehow but I can't figure it out

Mr. Liengme,

This will work for a running total on entire job, but as sheets are added it
changes the days before to the sum of all including sheets after. I need to
maintain the integrity of the total of the sheets before without adding any
sheets after.

"Bernard Liengme" wrote:

Before the first WORKSHEET ('tab' is the dohicky used to open a worksheet),
insert a blank worksheet called Alpha. After the last worksheet add a blank
worksheet called Omega.
Always insert new worksheet between the two
Use formula =SUM(Alpha:Omega!L20)
Of course, you can make up names that are more relevant to you
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"excelhurtsme" wrote in message
...
I am trying to reference the current tab name in a formula. I am trying to
get a running total across several different sheets, but here is the
catch,
those sheets have not been created yet. I have a template for a daily
report
that a person will copy sheet over and over until project done. I want the
formula for the running total to reference the tab name so as copied it
will
update this formula. Here is my formula =SUM(Day1:Day2!L20) Day1 and Day2
being the sheets, Day2 needs to be a general reference to current sheet
somehow but I can't figure it out

If you have 2 sheets this method will sum both. When you add a third, it
will be included in the sum. Is that not what you want?
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"excelhurtsme" wrote in message
...
Mr. Liengme,

This will work for a running total on entire job, but as sheets are added
it
changes the days before to the sum of all including sheets after. I need
to
maintain the integrity of the total of the sheets before without adding
any
sheets after.

"Bernard Liengme" wrote:

Before the first WORKSHEET ('tab' is the dohicky used to open a
worksheet),
insert a blank worksheet called Alpha. After the last worksheet add a
blank
worksheet called Omega.
Always insert new worksheet between the two
Use formula =SUM(Alpha:Omega!L20)
Of course, you can make up names that are more relevant to you
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"excelhurtsme" wrote in message
...
I am trying to reference the current tab name in a formula. I am trying
to
get a running total across several different sheets, but here is the
catch,
those sheets have not been created yet. I have a template for a daily
report
that a person will copy sheet over and over until project done. I want
the
formula for the running total to reference the tab name so as copied it
will
update this formula. Here is my formula =SUM(Day1:Day2!L20) Day1 and
Day2
being the sheets, Day2 needs to be a general reference to current sheet
somehow but I can't figure it out