Hi from a newb... I'm creating a file with numerous sheets and want to
automatically populate a cell with the sheet name. That way, if a
sheet name changes (which it does often) the cell in question will
populate with the new sheet name without any intervention from the
user.

TIA for your help!

See

http://www.mcgimpsey.com/excel/cell_function.html

In article .com,
"bjrbrain" wrote:

Hi from a newb... I'm creating a file with numerous sheets and want to
automatically populate a cell with the sheet name. That way, if a
sheet name changes (which it does often) the cell in question will
populate with the new sheet name without any intervention from the
user.

TIA for your help!

Better to do it with formulas. Go to the bottom of this page on Chip
Pearson's site:

http://www.cpearson.com/excel/excelF.htm

This doesn't work if the worksheet hasn't been saved at least once, but that
shouldn't be a problem in the situation you describe.

--
Regards,
Tom Ogilvy


"bjrbrain" wrote in message
oups.com...
Hi from a newb... I'm creating a file with numerous sheets and want to
automatically populate a cell with the sheet name. That way, if a
sheet name changes (which it does often) the cell in question will
populate with the new sheet name without any intervention from the
user.

TIA for your help!

If A1 is where you want to record the sheet name,

Range("A1").Value = Activesheet.name

Thanks for the suggestion but it doesn't seem to work for me. Keying
this into the cell with an '=' sign just produces a #NAME?

I will say that your solution is much more user friendly than another
one that was suggested:(for when someone else takes over my
spreadsheets):

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

What, pray tell, am I doing wrong in regards to your suggestion?

Thanks again,
Brian

Hard to tell - your formula works for me, though this is a bit more
efficient:

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

I can generate a #NAME! error if I switch the sheet to use R1C1-style
references. In that case the formula should be something like:

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


In article .com,
"bjrbrain" wrote:

Thanks for the suggestion but it doesn't seem to work for me. Keying
this into the cell with an '=' sign just produces a #NAME?

I will say that your solution is much more user friendly than another
one that was suggested:(for when someone else takes over my
spreadsheets):

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

What, pray tell, am I doing wrong in regards to your suggestion?

Thanks again,
Brian