can I dsplay the sheet name in a cell

Is there a way to display a sheet name in a
cell

[davers5]

This is an odd question. Within excel I do not know of a way that you can print the worksheet name in a cell. However, I just wrote this function to see if it would work and it pretty much did so you can try it.

Function WorksheetName() As String
ActiveSheet.Activate
WorksheetName = ActiveSheet.Name
End Function

Paste this in the visual basic editor and then you can write, in a cell, "=worksheetname()" and the name of the worksheet will appear in the cell. One caveat, though, the code gives instructions to print the active sheets' name in the cell, therefore, if you can find a way to view a sheet that's not the active sheet, the cell will print the wrong name. I don't know if you can even do that. Anyway, I hope this helps you a little bit.

Dave

[Aladin Akyurek]

On 2002-03-05 13:53, Anonymous wrote:
Is there a way to display a sheet name in a
cell

In B1 enter:

=CELL("filename",A1)

In C1 enter:

=RIGHT(B1,LEN(B1)-SEARCH("]",B1))

Note. B1 and C1 can be any other unused cells.

[davers5]

Or in one cell:

=RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename")))

Cell("filename"), I just learned, returns the path to your file including the current sheet that is active, with the workbook in brakets (so the end of the path looks like this: [book1]Sheet1). The right function cuts out the rest of the path and leaves you with just the sheet. However, if the workbook you are working with HAS NOT BEEN SAVED, the cell("filename") function will return a blank string (""). If you were having trouble with this, make sure you save the workbook.

Dave

[Aladin Akyurek]

On 2002-03-05 14:52, davers5 wrote:
Or in one cell:

=RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename")))

Cell("filename"), I just learned, returns the path to your file including the current sheet that is active, with the workbook in brakets (so the end of the path looks like this: [book1]Sheet1). The right function cuts out the rest of the path and leaves you with just the sheet. However, if the workbook you are working with HAS NOT BEEN SAVED, the cell("filename") function will return a blank string (""). If you were having trouble with this, make sure you save the workbook.

Dave

The reason I suggest using 2 cells is to avoid computing the same thing many times.

And,

=CELL("filename",A1)

is better than just

=CELL("filename")

Aladin

[Cosmos75]

A bit off the subject, but I got code from someone on how to show which sheet a cell is linked to. For example, if you have a cell in Sheet 2 linked to Sheet 1, you can have the adjacent cell tell you which sheet it is linked to.

http://www.mrexcel.com/board/viewtop...c=2229&forum=2

[Ricky Morris]

When using the cell("filename") function, it's safer to include ,a1 option because it assures that formula references the file and sheet the formula is on and not the file and sheet that you last did a recalc on.