Change sheet name but not the cell references

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
I have 5 sheets representing 5 days in a week. Each day i change the content of sheet 5 and then i have a table in a 6th sheet that orders data from the 5 sheets starting with sheet 1.

July 6 2010: some data from sheet 1
July 5 2010: some data from sheet 2
July 4 2010: some data from sheet 3
July 3 2010: some data from sheet 4
July 2 2010: some data from sheet 5

Now on July 7 I change data in sheet 5 as I only collect data from 5 most recent days

so now some reordering needs to follow...

sheet 1 (july 6) becomes sheet 2
sheet 2 (july 5) becomes sheet 3
sheet 3 (july 4) becomes sheet 4
sheet 4 (july 3) becomes sheet 5
sheet 5 (july 7) becomes sheet 1

but if i do this the formula's in the table of the 6th sheet change also so they keep referring to the original sheet. So after the renaming:

July 6 2010: some data from sheet 2
July 5 2010: some data from sheet 3
July 4 2010: some data from sheet 4
July 3 2010: some data from sheet 5
July 7 2010: some data from sheet 1

but that's not what I want I want to keep the formula's referring to the same sheets:

July 7 2010: some data from sheet 1
July 6 2010: some data from sheet 2
July 5 2010: some data from sheet 3
July 4 2010: some data from sheet 4
July 3 2010: some data from sheet 5

Hope u understand my problem...

2 replies

I guess I don't understand your problem. Because if you want to change this
July 6 2010: some data from sheet 2
July 5 2010: some data from sheet 3
July 4 2010: some data from sheet 4
July 3 2010: some data from sheet 5
July 7 2010: some data from sheet 1

into this

July 7 2010: some data from sheet 1
July 6 2010: some data from sheet 2
July 5 2010: some data from sheet 3
July 4 2010: some data from sheet 4
July 3 2010: some data from sheet 5

you just have to sort your data. And that's not where you are after right?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too