function to get active worksheet name

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
beachboy:

welcome to the baord!

if you mean a worksheet function, try:

=RIGHT(CELL("FILENAME",A1),LEN(CELL("FILENAME",A1))-FIND("]",CELL("FILENAME",A1),1))

Paddy
 
Upvote 0
On 2002-07-17 20:20, PaddyD wrote:
beachboy:

welcome to the baord!

if you mean a worksheet function, try:

=RIGHT(CELL("FILENAME",A1),LEN(CELL("FILENAME",A1))-FIND("]",CELL("FILENAME",A1),1))

Paddy

Hi Paddy:
I hope you wouldn't mind my butting in for throwing in the following alternative using the MID function:

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

it may be a little shorter than the one using the RIGHT function, but not a whole lot.

Regards!
 
Upvote 0
Yogi,

not seen your butt, but you should feel free to put it anywhere you like!

Paddy
 
Upvote 0
Alternatively, you can use a UDF to do the job.

==========
Function NM()

NM = Application.Caller.Worksheet.Name

End Function
===========

------------
Function NM()
Application.Volatile
NM = ActiveSheet.Name
End Function
------------

The second one will update the formula and return the name of the active sheet, no matter where it is called from in the workbook.

Bye,
Jay
 
Upvote 0
If no one minds me butting in either, you can insert a defined name and call it SheetName and refer it to:
=GET.CELL(5,A1)

Then in your worksheet you can call the function by setting any cell=SheetName

SheetName will return the name of that sheet.
 
Upvote 0
Al Chara

This doesn't work for me. Do I enter the formula
=SheetName
in another cell? When I do that, it returns 0. Any ideas? :confused:

Richard
 
Upvote 0
On 2002-07-17 22:56, RichardS wrote:
Al Chara

This doesn't work for me. Do I enter the formula
=SheetName
in another cell? When I do that, it returns 0. Any ideas? :confused:

Richard

This is what you do. Goto INSERT|NAME|DEFINE
Type SheetName under "Names in workbook:"
Type =GET.CELL(5,A1) under "Refers to:"

Then go into a cell on your worksheet and type SheetName
It will return the Sheet name
 
Upvote 0
On 2002-07-17 22:56, RichardS wrote:
Al Chara

This doesn't work for me. Do I enter the formula
=SheetName
in another cell? When I do that, it returns 0. Any ideas? :confused:

Richard
Richard,

You are right, I pulled the wrong function. This one returns the value of the cell. I have to lookup the correct XLM function to use.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top