Adding Tab Title to Cell Value 2

[MTBChik]

Hi there,

I have several worksheets in my Excel file and would like to know how I can do something like the &[TAB] function that is available in the Custom header/footer area and put that value in one of the cells in the worksheet.

Thanks!

__~o
`\<,
(_)/(_)
MTBChick
The day you stop riding is the day you die.

 

[SkipVought]

Hi,

Can't do that with native Excel functionality.

However, it can be done with VBA code. If you wish to pursue a coded solution, please post your question in Forum707.

Skip,
[sub]
[red][/red]
[/sub]

 

[xlhelp]

I take it you would like the sheet tab name in the cell.

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

Member- AAAA Association Against Acronym Abusers

 

[SkipVought]

lxhelp, you are exactly correct. I missed that one, going the other direction.

Skip,
[sub]
[red][/red]
[/sub]

 

[MTBChik]

Schweeeeet!

Thanks guys! I was going the VB route and hitting a wall.

__~o
`\<,
(_)/(_)
MTBChick
The day you stop riding is the day you die.

 

[xlhelp]

If you hit a wall while MTB'ing, it hurts even more.

Member- AAAA Association Against Acronym Abusers

 

[MTBChik]

Oops! Don't try this if the sheets are grouped though...

__~o
`\<,
(_)/(_)
MTBChick
The day you stop riding is the day you die.

 

[MTBChik]

Oh Bummer. Not working. Only works for one sheet. And they aren't grouped. When I change the cell on say the worksheet titled "January" it changes all 12 sheets A10 cell value to January instead of each sheet with it's own name.

BTW, ur right about the hitting the wall thing. I hit the dirt last week and am nursing a broken rib.

__~o
`\<,
(_)/(_)
MTBChick
The day you stop riding is the day you die.

 

[xlhelp]

Wouldn't the maco route be
activecell.value = activesheet.name ?

Member- AAAA Association Against Acronym Abusers

 

[xlhelp]

Change your formula to

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

Member- AAAA Association Against Acronym Abusers

 

[MTBChik]

Fahhhhbulous!

Even works when it's grouped (once everything has been populated on each sheet).

Thanks!

__~o
`\<,
(_)/(_)
MTBChick
The day you stop riding is the day you die.

 

[yogia]

Hi MTBChik:

In addition to the fine contribution from xlhelp, you may also use ...

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

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com