Cell Referencing Excel Tabs

ChrisO'Brien

New Member
Joined
Jan 23, 2013
Messages
13
Hi,

I have a workbook setup that is basically a summary page for loads of pages within a workbook and their values. The pages are referenced as normal i.e Sheet1, Sheet2 etc.

What I want to do is list the tabs on a summary page and have the list self update when the tab names are changed. Is this possible? I have seen other posts similar but they only seem to be able to make a cell look for the name of the tab its on and not display on from another page

I hope that made sense
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Hi Chris,

As far as I know you need code to do that. The below User defined Function (UDF) will convert a code name (found by looking at the sheet from with the Visual Basic Editor) to the sheet name you see in the workbook:

VBA Code:
Option Explicit
Function ReturnSheetNameFromCodeName(strCodeName As String) As String

    'Returns a sheet name based on its code name
    'Use as you would any native Excel formula i.e. =ReturnSheetNameFromCodeName("Sheet5")

    On Error GoTo EndFunction
    
    Application.Volatile 'A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet.
    
    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Sheets
        If ws.CodeName = strCodeName Then
            ReturnSheetNameFromCodeName = ws.Name
            Exit For
        End If
    Next ws
    
Exit Function

EndFunction:

    ReturnSheetNameFromCodeName = ""

End Function

Regards,

Robert
 
Upvote 0
Hi Robert, Thanks for the response but it looks a little complex for what I need.
I was hoping that I would be able to do something like click on a cell, tell it to equal a Tab Name as text or something.
With the solution you provided above I don't know how I would direct a specific cell at a specific tab name.
 
Upvote 0
Have a look here
 
Upvote 0
Hi All,

The best solution I have found is to create a cell on each page that pulls text from the the page tab.

Using A1 as an example I paste "=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)" into the cell which displays the tab name in A1.

I can now link my summary page cells to A1 of each tab simply by using "="

i.e. ='ANYSHEETNAME'!A1

The beauty of it is, when the tabs get renamed, the summary page self updates.

It may be a little long winded but it does the trick. Thanks for every ones help.
 
Upvote 0
You probably won't believe it but I just about to post something very similar :)
Glad you got it solved.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

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