If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
referencing current tab name in formula
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(Day1ay2!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 |
#2
|
|||
|
|||
referencing current tab name in formula
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(Day1ay2!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 |
#3
|
|||
|
|||
referencing current tab name in formula
....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(Day1ay2!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 |
#4
|
|||
|
|||
referencing current tab name in formula
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(Day1ay2!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 |
#5
|
|||
|
|||
referencing current tab name in formula
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(Day1ay2!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 |
Thread Tools | |
Display Modes | |
|
|