Use Absolute Sheet Name, even when the sheet name has been changed

[Kayslover]

Hi,

I have 2 workbooks (01 January.xlsm and Bar Area Taking Print.xlsx)

Initially in 01 January there are sheets called Sheet1, Sheet2,..Sheet5 and Totals.

In the Bar Area Taking Print.xlsx, there are 4 sheets called QTR1, QTR2, QTR3 and QTR4.

In the Bar Area Taking Print.xlsx, cell B4 refers to 01 January, sheet1, cell reference B4.

My problem is that all sheets starting Sheet in 01 January are renamed every year and therefore the reference in Bar Area Taking Print.xlsx doesn’t work, because it refers to Sheet1.

Is there any way that that the reference in Bar Area Taking Print.xlsx can still point to the absolute sheet name of sheet1 in 01 January.xlsm before it was changed?

The current formula in Bar Area Taking Print.xlsx is [01 January.xlsm]03-Jan-21'!$B$4, can the 03-Jan-21 still refer to Sheet1

Any help would be appreciated.

 

[JLGWhiz]

This is where logic comes into play. While the sheets in 01 January are renamed, If they remain in the same order and not sheets added or deleted, then you can use the index numbers of the sheet. eg. Sheets(1), Sheets(2), etc. Otherwise, you would have to edit your code each year when the names change to reflect those changes. However, if the changes are systematic rather than arbitrary, you might be able to programmatically accommodate the changes. Since there is no description of what the names are and how they are changed annually, I can't offer any advice on the programmatic changes.

 

[Kayslover]

No sheets are added or deleted to 01 January.

Cells are updated, and sheets are renamed.

 

[JLGWhiz]

No sheets are added or deleted to 01 January.

Cells are updated, and sheets are renamed.

As I said, you could refer to the sheets in the code by index number if they never change their order from left to right. Without more info on the sheet names, that is about the best I can offer for resolving your issue.
Regards, JLG

 

[Kayslover]

The sheet names in 01 January are dd-MMM-yy format.

Sheet1 for 2021 would be renamed to 03-Jan-21, sheet2 would be renamed to 10-Jan-21, sheet3, would be renamed to 17-Jan-21, sheet4 would be renamed 24-Jan-21 and sheet5 would be renamed to 31-Jan-21

I am attempting to change the current formula in Bar Area Taking Print from ='[01 January.xlsx]5-1'!$B$4 to ='[01 January.xlsx]Sheets(1)'!$B$4 as you suggested.

When I hit enter to accept the revised formula, I get a Microsoft Excel error which reads “We found a problem with one or more formula references in this worksheet. Check the cell references, range names, defined names, and links to other workbooks in your formulas are all correct”.

Where am I going wrong?

 

[JLGWhiz]

Sorry, it was not clear in the OP that you were referring to formulas. My suggestions were for using vba code.. I don't know of a way to handle sheet names that change when dealing with formulas. I don't work with formulas very much anymore and have forgotten a lot of what I used to know.

 

[Kayslover]

Thanks, sorry I wasn't clear up ahead.

 

[JLGWhiz]

It does seem that you could put the sheet names in specific cells and then reference those cells in the formulas, thus allowing the name changes to be made in those cells rather than the formulas. The formulas would always reference the same cells

 

[Kayslover]

I am sorry I don't understand what you are saying. Can you give an example?

 

[JLGWhiz]

TestBase.xlsm
	A	B	C
1
2		Sheet2
3			5
Sheet1

Cell Formulas
Range	Formula
C3	C3	=IF(A1="",INDIRECT(B2&"!A2"),"")

B2 contains the sheet name you want to use in the formula. C3 contains the formula, using INDIRECT to capture the B2 value and concatenating the cell reference A2 on Sheet2. This returns the value in cell A2 of Sheet2. If I change the name of Sheet2 to "Data" and change B2 To "Data" to reflect the changed sheet name I still get the value of cell A2 without changing the formula.

TestBase.xlsm
	A	B	C	D
1
2		Data
3			5
4
Sheet1

Cell Formulas
Range	Formula
C3	C3	=IF(A1="",INDIRECT(B2&"!A2"),"")

So instead of having to change all the formulas, I only have to change the cell with the sheet name when sheet names change.