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

TotallyConfused

Board Regular
Joined
May 4, 2017
Messages
247
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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.
 
Upvote 0
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
 
Upvote 0
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:
 
Upvote 0

Forum statistics

Threads
1,223,958
Messages
6,175,636
Members
452,662
Latest member
Aman1997

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
Back
Top