I want excel to retrive the name of the worksheet in a cell?

barry houdini

MrExcel MVP

Joined

Mar 23, 2005

Messages

20,825

• Nov 15, 2005

• #2

see this post from yesterday, specifically Aladin's suggestion

http://www.mrexcel.com/board2/viewtopic.php?t=179283&highlight=

 

Upvote 0

C

clausboss

Board Regular

Joined

Sep 13, 2005

Messages

71

• Nov 15, 2005

• #3

Barry thanks

I had some problems but it gave me a idee have to make it..

=MID(CELL("filename");FIND("]";CELL("filename"))+1;100)

Many thanks

 

Upvote 0

justme

Well-known Member

Joined

Aug 26, 2002

Messages

729

• Nov 15, 2005

• #4

how would you change Aladin's code to have the cell report the workbook name only?

 

Upvote 0

Greg Truby

MrExcel MVP

Joined

Jun 19, 2002

Messages

10,030

• Nov 15, 2005

• #5

justme said:

how would you change Aladin's code to have the cell report the workbook name only?

Click to expand...

I wouldn't.

I'd defined a named formula, WorkbookName and assign it the formula =Get.Workbook(16) Then in a cell you can just type =workbookname

 

Upvote 0

justme

Well-known Member

Joined

Aug 26, 2002

Messages

729

• Nov 15, 2005

• #6

how do you define a named formula? I typed in =Get.workbook(16) and it said That function is not valid.

 

Upvote 0

Greg Truby

MrExcel MVP

Joined

Jun 19, 2002

Messages

10,030

• Nov 15, 2005

• #7

Just like creating a named range, but instead of a range, you define a formula.

From the menu Insert | Name | Define... the name (WorkbookName) goes up top; formula (=Get.Workbook(16)) goes in the ReferTo box.

 

Upvote 0

justme

Well-known Member

Joined

Aug 26, 2002

Messages

729

• Nov 15, 2005

• #8

Nevermind, I got it.

Thanks!!

 

Upvote 0

justme

Well-known Member

Joined

Aug 26, 2002

Messages

729

• Nov 15, 2005

• #9

oops, I didn't see your post.

May I assume I can pull the folder name the same way??

 

Upvote 0

Greg Truby

MrExcel MVP

Joined

Jun 19, 2002

Messages

10,030

• Nov 15, 2005

• #10

Sorry, when you said "workbook name only" I took that to mean excluding the path. If you want the path, then just edit Aladin's formula to pull off the left, i.e.: <ul>[*]=LEFT(CELL("filename",A1),SEARCH("]",CELL("filename",A1)))[*]=LEFT(CELL("filename",A1),SEARCH("[",CELL("filename",A1))-1)[/list]

 

Upvote 0