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.

 

[Kayslover]

In Book 01 January, Sheet1, I have the sheet name being displayed by the formula =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) in cell X1. Therefore if Sheet1 is renamed to 03-Jan-21, then X1 displays 03-Jan-21

In Book Bar Area Taking Print, Sheet named QTR, cell B4 has ='[01 January.xlsm]Sheet1'!$B$4.

I cannot work out how to use the INDIRECT function to change Sheet1 to 03-Jan-21. I am not technically minded.

Help

 

[JLGWhiz]

Like i said, I am not proficient in formulas, but maybe somebody who is will pick up on the thread and help you out.
Regards, JLG

 

[Kayslover]

Thank you for assistance so far.

Hope somebody picks up this thread.

 

[Dossfm0q]

worksheets(index Number)
worksheets(1)
worksheets(2)

 

[Kayslover]

Hi,

PC is switched off @ the moment. Will try out your recommendations tomorrow and report back.

Thanks for your input.

 

[Kayslover]

Hi,

Tried out you recommendation:-

The current formula in workbook Bar Area Takings is:-

='[01 January.xlsm]03-Jan-21'!$B$4

Where 03-Jan-21 is the first worksheet in workbook 01 January.

I have changed the formula to:-

='[01 January.xlsm]worksheets(1)'!$B$4

And I get the following error when hitting enter to accept the formula:-

“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”.

Any suggestions?

 

[TotallyConfused]

Hello Kayslover

I see you are a new member so let me WELCOME you. This place is a gold-mine of information, so I hope you enjoy your time here.

That is an interesting problem you have. I do know that if a sheet is renamed, then Excel will go through that workbook and update all formula's that referenced that sheet. Apparently this feature does not apply if another workbook is involved. I'm wondering, and I have NO idea if this would work or not, but it seems to me that somewhere in the setup (or configuration options) for Excel, there is the option to have Excel update all links. If this were to be turned on, if it isn't already, would that allow Excel to update the formula's in your second workbook, and therefor reflect the new sheet names? As I stated, I have NO idea if this would be of any help or not, but is just something I thought of. Be sure to test this on a copy of your data in case something goes wrong.

I'll be following this thread to see what happens in case I ever run into a problem similar to yours. Good luck!

TotallyConfused