reference tab name (2003 SP3)

Topic

New Reply

I think this is probably an easy question. Is there a way to make a cell reference the name of a tab (another worksheet)? How?

Thanks!

Reply | Quote

Replies

Only with VBA code.

A line of code similar to: Activesheet.Name = Range(“A1”).Value

It will need to be linked to a worksheet event if it must happen more automatically. If so…please specify more detail of what you want to have happen.

Reply | Quote

If you meant that you want to return the sheet name of a cell, you can use a formula like this:

=MID(CELL("filename",Sheet2!A1),FIND("]",CELL("filename",Sheet2!A1))+1,256)

This will only work if the workbook has been saved to disk, not in a newly created workbook.

Reply | Quote

Wow…and I read that question exactly the other way around. This could have been in the puzzle forum.

Reply | Quote

I’m not sure what Michael’s question means – I just wanted to provide an alternative.

Reply | Quote

What a useful formula, it solves a problem I didn’t even know I had.

Thank you.

StuartR

Reply | Quote

..so use cell(“address”,
..instead of cell(“filename”,
and tweak formula a bit to get rid $ address etc (find “!” perhaps?) and make allowances for spaces in sheet names and then..
..I think it will work even if the workbook has NOT been saved to disk and will work in a newly created workbook

zeddy

Reply | Quote

Thanks. On the other hand, the formula I posted will also work when the cell is on the same worksheet, while CELL(“address”, …) will only return the cell address without the sheet name if the cell is on the same sheet.

The OP can choose which formula suits his purposes best.

Reply | Quote

Hi Hans

I knew you knew that.

zeddy

Reply | Quote