Free Microsoft Excel 2013 Quick Reference

Sheet tab name in cell

Is there a way to reference a sheet name as a variable from a cell?

For instance, I am trying to set up a way to color the sheet tab based on what name is input in say Cell A1. So if "sheet1" is selected (from a list of sheetnames), then sheet1 tab color would change to red. Is this possible?


Post your answer or comment

comments powered by Disqus
Hi all,
what i would like to do is have a macro that will open a worksheet from sheet 1 cell A1
also this is a hidden sheet
in this cell i have the sheet tab name i would like to open can this be done
hope some one can help me

Many Thanks
Helen

Hi,

does anybody know how to insert the title of the sheet tab into a cell on that sheet tab.

regards,

Paul.

how do I place the sheet tab name in my spreadsheet by formula?

Excel 2003

I'd like to use the sheet tab name in a formula. I'd actually like to fill
in some fields including the sheet tab name. Is there an easy way to do
this?

Thanks in advance.

Barb Reinhardt

Can anyone tell me if there is a way for me to display the Name of a Sheet
Tab in a cell.

Take for example I have 4 worksheets, labelled Shawn, Kevin, Mary, & Data.
In data, I want it to show the name of the worksheets. So in Cell A4 I
would like it to say Shawn, then in B4 I can put Shawn's data in. Is there
a function or a formula that allows me to do this.

Thanks for any help.

I would like to have the sheet tab name put into another sheet, say in cell A1. I have a large file which I want to summarize the names of the sheets into one sheet. And, I need the information from the sheets summarized into the same sheet. So, if sheet 1 is named Cost, then the name and the amount is listed in the summary sheet. Thanks for your help. And, by the way, this is the BEST forum I have found for this kind of information. Great Job Guys!

I have a rather large workbook. I would like to have a link to each tab on
the first tab (worksheet) in the workbook. What I envision is a cloumn of
cells with the sheet names in cells. When the cell or text is clicked on, the
user is taken to the corresponding worksheet.

Also, is there a way in VBA to get the tab names in order to populate the
cells?

Thanks

Using a formula, is there a way to display worksheet (tab names) in a cell?
For example, I have 3 worksheets with names One, Two and Three. In cell A1 in
each woksheet, I would want to see the worksheet name for whichever worksheet
I am viewing. I do not want the name to change in the cell each time I click
on one of the other two spreadsheets, similar to what happens with
=CELL("FILENAME"). I also do not want to see the entire path for the
worksheet.

I currently use both Excel 97 & Excel XP, is there a way by formula to
include the worksheet tab name in a cell similar to &[tab] in the
header/footer?
--
Mark

No question here, just a procedure for the archive.

Search criteria: worksheet tab names report return worksheet tab names
return tab names return worksheet chart names return tab names
return chart sheet names report sheet tab names report chart names
return all worksheet names return all chart sheet names return all chart names
list all tab names list all worksheet names list all chart sheet names list all
chart names

Sub SheetTabAndChartTabNamesReport()

'Return all worksheet tab and chart tab names to a new worksheet
On Error Resume Next
ActiveWorkbook.Worksheets.Add.Name = "WkSheetNamesReport"
Sheets("WkSheetNamesReport").Select
Range("A1").Select
Dim iSheet As Long
Dim iChart As Long
For iSheet = 1 To ActiveWorkbook.Worksheets.Count
ActiveCell.Offset(iSheet - 1, 0) = "'" & Worksheets(iSheet).Name
Next iSheet

For iChart = 1 To ActiveWorkbook.Charts.Count
ActiveCell.Offset(iChart - 1, 0) = "'" & Charts(iChart).Name
Next iChart

End Sub

Version: Microsoft Office Excel 2003

How can I repeat a sheet's tab name in a cell?

I have a summary sheet which has data fed from multiple other supporting
sheets. The supporting sheets are updated by other users and they modify the
tab name so it reflects their specific subproject effort.

Just as the summary sheet reflects the supporting sheet data, it would be
very helpful to state the tab name so it is clear what the data is
representing (i.e.- the subproject sheet name). I wish to create a link so
if the subproject (tab) name is changed, the summary sheet is automatically
updated.

I had hoped this would be similar to adding the file name and path in a
cell: =cell("filename") however, it is not.

Im sorry there is an old link on this exact title, and the venerable Mr. Hawley did answer it..problem is i tried it and cannot make it work the way i expected.

http://www.ozgrid.com/forum/showthread.php?t=60950 the code is listed below for convenience

$A$2 contains a formula...the code doesnt work on a formula. if i overwrite the formula, THEN the code works and the sheet tab is named to whatever i type in the cell

but how do i make it work with a formula?


	VB:
	
  Range) 
    Dim strNew As String, strOld As String 
    If Target.Cells.Count > 1 Then Exit Sub 
     
    If Target.Address = "$A$2" Then 
        strOld = Me.Name 
        strNew = Target 
        On  Error Resume Next 
        Me.Name = strNew 
        On Error Goto 0 
        If Me.Name = strOld Then 
            MsgBox "The name " & strNew & " is not a valid  Sheet name" 
        End If 
    End If 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Version: Microsoft Office Excel 2003

How can I repeat a sheet's tab name in a cell?

I have a summary sheet which has data fed from multiple other supporting
sheets. The supporting sheets are updated by other users and they modify the
tab name so it reflects their specific subproject effort.

Just as the summary sheet reflects the supporting sheet data, it would be
very helpful to state the tab name so it is clear what the data is
representing (i.e.- the subproject sheet name). I wish to create a link so
if the subproject (tab) name is changed, the summary sheet is automatically
updated.

I had hoped this would be similar to adding the file name and path in a
cell: =cell("filename") however, it is not.

I have some data on a worksheet and in cell A2 I want to type in a person's name. Is it possible for that sheet tab to automatically update with the name I have typed in cell A2?

Thank you so much in advance for your help!!

Hi Excelling,

Look on Chip Pearson's formula page under Miscellaneous for an example of
how to do this:

http://www.cpearson.com/excel/excelF.htm

--
Regards,

Jake Marx
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Excelling in MN wrote:
> Version: Microsoft Office Excel 2003
>
> How can I repeat a sheet's tab name in a cell?
>
> I have a summary sheet which has data fed from multiple other
> supporting sheets. The supporting sheets are updated by other users
> and they modify the tab name so it reflects their specific subproject
> effort.
>
> Just as the summary sheet reflects the supporting sheet data, it
> would be very helpful to state the tab name so it is clear what the
> data is representing (i.e.- the subproject sheet name). I wish to
> create a link so if the subproject (tab) name is changed, the summary
> sheet is automatically updated.
>
> I had hoped this would be similar to adding the file name and path in
> a cell: =cell("filename") however, it is not.

I want to have a sheet tab name be automatically changed to the value in a cell on that sheet. For example, if A1 value is Jan 07, then the sheet tab name would be Jan 07. If I change A1 to Feb 07, then the tab name would change.

I need help coming up with VBA that will put the Sheet's file name & a dash with the sheet name in cell M1 when the sheet's name is changed (right click sheeted tab & rename).

For example:

When changing tab named Sheet2 to 08-39 for the workbook TaxWorkSheets.xls,

put in cell M1:

TaxWorkSheets.xls-08-39

I really appreciate you help. mikeburg

i have a budget with a bunch of sheets. The first sheet (sheet1) is to be a
summary sheet.

In the summary sheet I'd like to be able to make a cell name dependent on
the tab name of other sheets. That way I only have to change the name in one
place.

For instance in the summary sheet the first column will be all the sheet names

item1 (sheet 2 name)
item2 (sheet 3 name)
item3 (shhet 4 name)

where each item is a sheet name. If there a formula to do it?

Hello all -

I am trying to do the following:

Sheet 1 contains:

Item Sheet 2
ABC =vlookup(A2,'Sheet 2'!$A:$B,2,false)

Sheet 2 contains:

Item Data
ABC 2

I'm trying to get the vlookup to return the value "2"

Right now, I'm manually entering the tab name in the vlookup function, even though it's contained in cell B1.

The tabs are contained in the same workbook if that matters. Since this workbook is growing rather quickly, this is a painful process and doesn't feel very scalable. Since I'm using a mac, I need to do this with functions vs. macros. Does anybody know how I can reference a cell for the name of a tab in the vlookup function?

I was thinking I could maybe somehow do this with the INDIRECT function but I'm stumped.

I apologize if this wasn't clear but it seems to be a slightly convoluted question.

Thanks in advance for the help.

Excel - I would like to refer to the sheet tab name on the spreadsheet...I
know you can use the simple (tab) command in the header, but what would be
the "formula" you would put in a cell to refer to the sheet tab name...ie
={tab}?

Hello all,

I have two separate workbooks with an equal number of worksheets. In workbook 1, each sheet has a person's name (John Doe, Susan Smith, etc.) in cell c6. In workbook 2, each sheet is named after one of those people. That is, workbook 2 has worksheet tabs John Doe, Susan Smith, etc. The worksheets in workbook 1 do not have cooresponding names, but they could have if it made the solution simpler.

I want to perform the same set of calculations on each sheet in workbook 1, by including in the formulas the related data from workbook 2, based on the match between the name in cell c6 of each sheet in workbook 1 and the tab name in workbook 2.

I would prefer a link formula, but would be willing to use a macro. I do not think I need anyone to write the macro for me. Whether link or macro, I only need to know how, if possible, the reference is coded.

I don't know if what follows will help or confuse, but essentially, I want to incorporate a set of calculations into each sheet of workbook 1 of the following nature:

content of wb1 sheet 1 cell m20: = value of wb1 sheet 1 cell m7 * value of wb2 cell a20 on sheetname with value of wb1 sheet 1 cell c6.
content of wb1 sheet 1 cell m21: = value of wb1 sheet 1 cell n7 * value of wb2 cell a21 on sheetname with value of wb1 sheet 1 cell c6.
content of wb1 sheet 1 cell m22: = value of wb1 sheet 1 cell o7 * value of wb2 cell a22 on sheetname with value of wb1 sheet 1 cell c6.

content of wb1 sheet 2 cell m20: = value of wb1 sheet 2 cell m7 * value of wb2 cell a20 on sheetname with value of wb1 sheet 2 cell c6.
content of wb1 sheet 2 cell m21: = value of wb1 sheet 2 cell n7 * value of wb2 cell a21 on sheetname with value of wb1 sheet 2 cell c6.
content of wb1 sheet 2 cell m22: = value of wb1 sheet cell o7 * value of wb2 cell a22 on sheetname with value of wb1 sheet 2 cell c6.

Thanks in advance to anyone who can help.

Conor

I have 68 sheets within a workbook in which I named 1, 2, 3 etc.. All the
sheets are formatted the same ready for data to be entered, and I would like
to be able to assign the tab name to one of my cells for each worksheet as
you can do in the Header Footer.

Rather than the typist having to type in Panel # on every sheet, I would
like for it to reference the sheet nuimbery help would be greatly appreciated.

Thanks

Can anyone give me a piece of code that will apply a name in cell d3 to the sheet tab?

Then scroll through the workbook doing the same until all the sheet tabs have been named.

Many thanks in advance.

in cell A1 can you display the sheet tab name so that it the sheet is renamed cell A1 will show the current sheet tab name?


No luck finding an answer? You could always try Google.