×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Repeating the worksheet name into a cell

Repeating the worksheet name into a cell

Repeating the worksheet name into a cell

(OP)
I use a spread sheet to track projects and invoices, I will have several projects in one spread sheet with several worksheets so that I can share similar values from a master worksheet "BUDGET REPORT" into the appropriate places in each project's worksheet. Currently I do this by using the following formula in the cells "=CONCATENATE('BUDGET REPORT'!E18" This formula will repeat exactly what's in the referenced cell, text or numbers, without displaying a "0" when the referenced cell is blank. Since my worksheet tabs are named for each project, I could save some incorrect references, and therefore confusion, if I could find a formula that would repeat the name from the worksheet tab into a cell. Does anyone know a way to do this?

RE: Repeating the worksheet name into a cell

You need some VBA to do this, and make your own user-defined function:

CODE

Function SheetName(R As Range) As String
    SheetName = R.Worksheet.Name
End Function
This will return the name of the worksheet that the range is on. So, =SheetName('BUDGET REPORT'!E1) will return "BUDGET REPORT", and =SheetName(A1) will return the name of the active sheet.
Alternatively, you can download ASAP utilities, it has among 1000 other things this same function built in ( =ASAPSheetName() ), which is probably a similar single-line piece of code as above, except that it returns the active sheet rather than the sheet of the range reference.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

RE: Repeating the worksheet name into a cell

Consider playing with the CELL function.  The following will return the filename & tab as a single reference

=CELL("FILENAME",A1)




RE: Repeating the worksheet name into a cell

The following formulae can be used to get worksheet name
assuming they are entered in B1 and C1

CODE

=CELL("filename",A1)
=MID(B1,FIND("]",B1,1)+1,1000)

Alternatively you can use one of the leftover functions from Excel 4 macro language as follows:

Use the Insert:Name:Define menu item to open the define Name dialog.  
In the "Names in workbook:" box put any allowable name.  say "MyFileName"
In the "Refers to:" box add the following formula
=Get.Document(1)
click ok

Now use =MyFileName anywhere in the worksheet where you want to display the workbook and sheet name

RE: Repeating the worksheet name into a cell

(OP)
Thanks all, but here are the results I'm getting.

joerd, I don't understand VBA but what I did was open TOOLS, MACROS, VISUAL BASIC EDITOR and pasted your code in the window. I then typed =sheetname(A1) into a cell and it returned #NAME?.  I really don't know anything about VBA so I'm sure I'm doing that part wrong.

bltseattle, This gets the closest to what I'm trying but it returns the entire fiepath and filename along with the sheet name and I don't know how to shorten it to just return the sheet name.

cummings54, I placed the second code you gave in cell B1 and it gave a circular reference error, and when I copy it to any other cell it returns #VALUE! I used the Macro you gave and it returns the filename and worksheet name, but at least not the entire filepath. This is closer to what I'm looking for but still not quite it.

Can anyone tell me where I'm going wrong? Thanks

RE: Repeating the worksheet name into a cell

Put
=CELL("filename",A1)
into cell B1 (cell B1 for consistency with the rest of this discussion - you can put it anywhere out of sight or reference any cell besides A1 if you want).

Then put this slightly revised formula
=MID($B$1,FIND("]",$B$1,1)+1,1000)
into cell C1 (or any cell, for that matter - the absolute reference back to B1 or wherever you put the first formula will keep the #VALUE messages at bay.


Norm

RE: Repeating the worksheet name into a cell

(OP)
Thanks Norm, It works perfectly.  I was misinterpreting what cummings54 had suggested.  It works his way also, I just didn't apply it correctly.

good job

RE: Repeating the worksheet name into a cell

You can make this formula 'self-contained' by using the sungle formula:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1),1)+1,1000)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources