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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,730
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
 

ChrisO'Brien

New Member
Joined
Jan 23, 2013
Messages
13
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.
 

ChrisO'Brien

New Member
Joined
Jan 23, 2013
Messages
13

ADVERTISEMENT

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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,340
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,730
You probably won't believe it but I just about to post something very similar :)
Glad you got it solved.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,151
Messages
5,623,043
Members
415,948
Latest member
swart430

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