Can a value be transferred from one sheet to another using the Sheet Code name?

[TotallyConfused]

I have a blank workbook with two sheets that Excel created, originally called 'Sheet1' and 'Sheet2'. Then I renamed these to be called 'TEST1' (formally Sheet1) and 'TEST2' (formally Sheet2). I have a number in TEST1 cell 'A1' that I need to copy over to TEST2 cell 'A1'. In Sheet2 cell 'A1' I placed the formula: ='TEST1'!A1 and this copied the number exactly as I wished. When I tried to modify this formula to read: ='SHEET1'!A1 all I receive is the error message of #REF!. I then tried using the same formula, except removing the two apostrophes, but the end result was still the same error message. If possible, I would like to use the sheet code name rather than the tab name, so that if someone changes the sheet tab name, the end result won't be disastrous. Also I'd like to stay within Excel if possible, rather than going to VBA. Can this be done, and if so, how would I word the formula?
Thank you in advance for any help or advice you may be able to offer.
TotallyConfused

 

[Eric W]

If you stay within Excel, you need to use the Excel sheet names. If someone changes a sheet name, Excel will search all the formulas, and change any references to the old sheet name to the new sheet name, so there shouldn't be a problem. The only issue is if you use INDIRECT to reference another sheet, and the sheet name there is a text value. Excel won't know to change that.

 

[TotallyConfused]

Hello once again, Eric

I'll be darned! I just now returned to my computer and read your answer to my question with almost disbelieving eyes. It had never occurred to me in my wildest dreams that Excel would, or even could, automatically change a formula. I set up another workbook similar to the one described in my original question. I not only changed the Test1 sheet name but also changed the number, yet everything worked exactly as you said it would. You Sir, are a genius and I can't begin to THANK YOU enough for the help you've given me today with my two questions. This one and also the one earlier today where you explained the advantage of using the 'LIKE' operand in an 'IF' statement.

It never ceases to amaze me as I use Excel even in my small way, at the immense range of things it can do. There are some very brilliant programming minds at Microsoft that dreamed up this program with all its features. I'd love to know the total number of lines of code that have been written to make all this happen.

Once again, THANK YOU Sir for your help. You take care and keep yourself safe. You sure have earned two 'thumbs up' from me today.

TotallyConfused

 

[Eric W]

I agree, I'm constantly amazed at what Excel can do. Some of it was designed into it by some bright people at MS, some of it was devised by people using it who were thinking outside of the box. After over 25 years of using it, I'm still learning new things.

Anyway, I'm glad to help!