How to Add a Tab to an excel formula

  • Thread starter Thread starter Janly
  • Start date Start date
J

Janly

Help,

I have a workbook with 28 Tabs, each listing employees name, hr
worked, salaries, benefits, and total. Each Tab represents a pa
period and I have a Summary, & Summary to date tabs. All Tabs are se
exactly the same. For past pay periods it has the actual data. Fo
future pay periods it has estimated data.

The summary tab is no problem, is just the sum of all tabs. My dilemm
is in the Summary to date Tab. I want to include only the tabs tha
has actual costs, but I don't want to have to change the formula i
each cell every time the pay period changes.

How can I change the tab reference to the formulas without having to g
to each cell and update the formula?

Ex: A cell is currently set to "sum('PP01:PP*03*'!A1)". I need t
change PP03 to PP04 on next pay period, and so on.

Is there a way where I can change the Tab's name in one cell and i
will replicate in all other cells, without changing the cell reference
And without having to write an extensive IF formula or doing a searc
and replace each time?

I will appreciate any assistance on this.

Ale
 
One solution would be in insert a blank worksheet named Current, between
PP01 and PP03.

Change the formula to =SUM('PP01:Current'!A1)

When you want to include PP04, move the Current sheet to the right of
the PP04 worksheet.
 
Thanks! My inspiration, as usual, is, "How can I avoid lots of work?"

Dave said:
Neat idea.

That's the out-of-box thinking that has, er, taken you out of the box!
Click to expand...
 
Worked like a charm. Thanks much.


Debra said:
*One solution would be in insert a blank worksheet named Current
between
PP01 and PP03.

Change the formula to =SUM('PP01:Current'!A1)

When you want to include PP04, move the Current sheet to the righ
of
the PP04 worksheet.
Click to expand...
 
deniseS said:
*Is there a way to set a formula to calculate how many rows
above (COUNT) with no text or numbers in the column? *
Click to expand...

Insert your range into =ROWS(), eg =ROWS(A1:A20) will give you a numbe
of 20
 
Back
Top