Sheet Name Formula (should be an easy one)

[LarryM]

I'm close but can't figure out how to get the name of a worksheet to appear in a cell using a formula. I know it's going to take an Indirect statement but can't figure it out.

I have several sheets that look alike but have different sheet names (states). So, in my lookup formulas, I want to use something like Vlookup("Alabama",Lookup_Range,2,0) where Alabama is the name of the sheet. That way, I can copy that formula to the other sheets with the different state names.

Essentially, I'm looking for the simple formula to show the name of the sheet in a cell.

I always appreciate the help.

 

[Jonmo1]

This will return the Sheet Name

=REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),"")


Though I'm not sure I understand your purpose for this, why you would use Indirect.


Note, the formula will error if the file has not yet been saved.

 

[GK039]

Or something like:

=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,"]","*")))

George

 

[T. Valko]

This will return the Sheet Name

=REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),"")


Though I'm not sure I understand your purpose for this, why you would use Indirect.


Note, the formula will error if the file has not yet been saved.

If you don't include a cell reference that formula will return the name of the sheet that was active when the last calculation took place.

Enter that formula on Sheet1.

On Sheet2 enter this formula in cell B1: =A1.

Navigate back to sheet1 and check to see what sheet name was returned.

 

[T. Valko]

I'm close but can't figure out how to get the name of a worksheet to appear in a cell using a formula. I know it's going to take an Indirect statement but can't figure it out.

I have several sheets that look alike but have different sheet names (states). So, in my lookup formulas, I want to use something like Vlookup("Alabama",Lookup_Range,2,0) where Alabama is the name of the sheet. That way, I can copy that formula to the other sheets with the different state names.

Essentially, I'm looking for the simple formula to show the name of the sheet in a cell.

I always appreciate the help.

Try this...

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

Note that the file must have been saved at least once and given a name.

 

[LarryM]

First, thank you for the quick replies and for the forumlas. Valko, you nailed it when you mentioned that the formula would change to the active sheet. I need to read through all this and find the one that works. I need the formula to be on each sheet with the name of that sheet shown.

The reason I'm doing this in short is that the names of the tabs reference the state but are not the same name that is used in the lookup array. So, I have a table with the correct full names and need to convert names like "FL" to "Florida" where FL is the sheet name.

I'll repost if these formulas don't do the trick but I think they will. Thanks everyone!

 

[Scott Huish]

=REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"")

 

[LarryM]

Ok everyone, the winning formula in this case is

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

I have no idea how that's working but for now, it does the trick.

Much appreciated you all!

 

[T. Valko]

Ok everyone, the winning formula in this case is

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

I have no idea how that's working but for now, it does the trick.

Much appreciated you all!

You're welcome. Thanks for the feedback!

 

[LarryM]

Oh, and now I figured out what it's doing. The 31 is because 31 characters at most can fit in the sheet name. Great formula. Thanks again.