Updating Sheet References

Each week I have to create a sheet with the week for example I start with the following for the workbook

existing sheet name: week 1, week 2

In week two I have cells with a bunch of formula calculating from week 1 such as =E6+'week1'!E13, =F6+'week1'!F13

New week begins I have to create another sheet name week 3. so my calculation for this cells need to be =E6+'week2'!E13, =F6+'week2'!F13

I have about 30 cells that needs to have these calculations. On week 3, I was thinking to have a cell where I input the sheet name and then when I input the sheet name on cell for example week 2. It will automatically change anything that says week 1 in the calculation automatically which means my calculation will be for week 2.

Hope that clears what I'm trying to do.

Again thanks for the Help,

Lostboyca

First up - I modified your title slightly - it was previously a little vague - try to be as specific as possible.

There are a few options - one would be to just run an Edit -> Replace when you first create the new sheet (replacing old references with new).

The other would be to use INDIRECT in your 30 calculations but pending the dependants thereof this may (may not) have an impact on overall model performance - with the info. we have we simply don't know.

Please Login or Register  to view this content.

where A1 holds sheet reference - eg "week 2"

If you need further assistance please post a sample file to illustrate what you have presently.
It's more than likely that you would want to revise the INDIRECT approach for sake of copying / pasting to the other cells but we would need to know the location of each formula to offer a more dynamic approach.

Nice thanks a million that worked. I new it was wit the INDIRECT but a month of messing with it and still couldn't get it to work