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!:)
[Solved] Sheet tab name to cell on sheet
Here's a function you want to be familiar with:
=cell("filename") ... and options for using it.
will show your entire PATH, FILE and SHEET name as, for example:
C:Documents and Settingscln01My DocumentsApplication Tips[Some File.xls]VLookup Example (this is the file I have set up as an example)
=LEFT( MyFile, FIND( "[", MyFile) - 2)
will show the PATH only, such as:
C:Documents and Settingscln01My DocumentsApplication Tips [from the same file as above -- where I have a defined name of MyFile = cell("filename")]
=MID( MyFile, FIND( "[", MyFile) +1, FIND( "]", MyFile) -1 - FIND( "[", MyFile))
shows the FILE name only, or
=RIGHT(MyFile, LEN(MyFile) - FIND("]", MyFile))
returns the TAB name only, or
No, these are not real simple formulas, but I have them grouped in a workbook that I can refer to (that further simplifies things with more Named Ranges), if my attachment works.
I just realized that I may not have answered your question, since it appears that you want to list 'other' sheet names in 'this' sheet. It's very simple. Refer to what I showed you above, and just include the other sheet by reference, such as:
Chris is correct, but you may want something simpler to use that will perhaps be easier to use to reference sheets other than the one that's active. The following UDF should do it for you.Code
Function SheetName(r As Range) As String 'returns the sheet name of the range SheetName = r.Parent.Name End Function
For example, Sheetname(sheet3!a1) will return Sheet3.
If your sheetnames are static you could use the followingCode
Sub ListSheetnames() Dim intIndex As Integer For intIndex = 1 To ActiveWorkbook.Sheets.Count ActiveCell.Offset(intIndex - 1, 0) = ActiveWorkbook.Sheets(intIndex).Name Next End Sub
This will populate from the activecell down each sheetname in the book.
so make sure the cells below are empty!
Hi all, Just FYI :))
Don’t have an account yet? Register yourself now and be a part of our community!