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

TotallyConfused

Board Regular
Joined
May 4, 2017
Messages
246
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,770
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

Board Regular
Joined
May 4, 2017
Messages
246
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,770
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! :cool:
 

Watch MrExcel Video

Forum statistics

Threads
1,127,861
Messages
5,627,314
Members
416,239
Latest member
Counselor85027

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top