Also, referencing cell B4 of the first sheet in another Excel file can be done like this:
However note that the file containing the VBA script needs to be stored as a macro file, .xlsm
For many Excel users, they tend to habitually rename the default worksheet name to their needed one. But in many cases, they need to reference the worksheet based on its index number rather than its real name. How to achieve it? You can try the method in the article.
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
Please do as follows to reference worksheet by index number instead of name in Excel.
1. Press Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.
2. In the Microsoft Visual Basic for Applications window, click Insert > Module. Then copy and paste the below VBA code into the Module window.
VBA code: Reference worksheet by index number in Excel
Function SHEETNAME(number As Long) As String SHEETNAME = Sheets(number).Name End Function
3. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window.
1. If you need to reference a certain sheet name with its number, please select a blank cell, and enter formula =SHEETNAME(1) directly into the Formula Bar, then press the Enter key. See screenshot:
2. If you want to get a cell value from a worksheet based on its index number, please use this formula =INDIRECT("'"&SHEETNAME(1) &"'!A1").
3. And if you want to sum a certain column in a worksheet based on its index number, please apply this formula =SUM(INDIRECT("'"&SHEETNAME(1) &"'!C2:C7")).