How to Add a Tab to an excel formula

[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('PP01P*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

 

[Debra Dalgleish]

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.

 

[Dave Peterson]

Neat idea.

That's the out-of-box thinking that has, er, taken you out of the box!

 

[Debra Dalgleish]

Thanks! My inspiration, as usual, is, "How can I avoid lots of work?"

Neat idea.

That's the out-of-box thinking that has, er, taken you out of the box!

 

[Dave Peterson]

I was going to reply, but it would have taken too long.

Thanks! My inspiration, as usual, is, "How can I avoid lots of work?"

<<snipped>>

 

[Janly]

Worked like a charm. Thanks much.

*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.

 

[Andrew]

*Is there a way to set a formula to calculate how many rows
above (COUNT) with no text or numbers in the column? *

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