Repeating the worksheet name into a cell 1

[draftsmandon]

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?

 

[joerd]

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

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.

 

[bltseattle]

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

=CELL("FILENAME",A1)

 

[cummings54]

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

=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:Nameefine 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

 

[draftsmandon]

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

 

[NormPeterson]

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

 

[draftsmandon]

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

 

[tinfoil]

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

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