Cell reference to a worksheet.

[gibbsak]

Is there a formula to link a cell to return the label of a particular worksheet?

I have several worksheets and each has a title. I would like the titles to be filled automatically based on the worksheet name found in the tabs at the bottom.

Thanks

 

[VoG]

Try

<code>=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

the file must be saved for this to work.
</code>

 

[Darren Bartrup]

Something like:

=RIGHT(CELL("filename",Sheet5!$N$1),LEN(CELL("filename",Sheet5!$N$1))-FIND("]",CELL("filename",Sheet5!$N$1)))

The function CELL returns specific information about the referenced cell.

The argument "filename" returns the full path, including the sheet name of a referenced cell within a saved workbook.

The key here is that the workbook name is enclosed in square brackets [ ], and anything that appears after the closing bracket is the sheet name, so the formula finds the closing bracket (]) and returns anything to the right of it.

 

[gibbsak]

Try

<CODE>=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

the file must be saved for this to work.
</CODE>

Thank You

That worked great.

 

[snb_]

UDF:

Function sheetname()
    sheetname = ActiveSheet.Name
End Function

in B5:
=sheetname()

or

=Mid(CELL("filename");FIND("]";CELL("filename"))+1;200)