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
CODE
SheetName = R.Worksheet.Name
End Function
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
=CELL("FILENAME",A1)
RE: Repeating the worksheet name into a cell
assuming they are entered in B1 and C1
CODE
=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
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
=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
good job
RE: Repeating the worksheet name into a cell
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1),1)+1,1000)