how to make a particular cell assume the name of the worksheet

Hello everyone,

I'm quite new to excel. I wish to know which command or formular to apply on a cell to make it assume the name of the worksheet. For instance, if the name of my worksheet is 'Joa', and I want cell A1 to take the value 'Joa', what do I do?

I also wonder if there's a way I can fill a particular cell (say A1) of all the sheets I have with a certain value. For instance, If I want all cell A1 to carry the value 100, and all B1 to carry date, how do I go about it? I'm working with over 30 sheets, and its a pain inserting the date in all sheets one after the other. A single command to execute this task would be really helpful.

Thank you for your assistance.

you could build a macro function
usage: =SheetName()

Function SheetName()
SheetName = ActiveSheet.Name
End Function

Hello Ranman,

Thank you for your swift response.

I think you've proffered a rather advanced solution. As a neophyte in excel, creating a macro function could be a bit daunting. Nonetheless, I don't mind learning how to do that.

I'd do a little research here. I'd welcome a link to a thread that has a clear-cut explanation of how to create macro function.

=cell("filename) in the sheet will give you the full address of the sheet, incl the sheet tab.

then you just need to extract the sheet tab as required

try this
=MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,LEN(CELL("filename",$A$1))-FIND("]",CELL("filename",$A$1)))

Originally Posted by AlanY

=cell("filename) in the sheet will give you the full address of the sheet, incl the sheet tab.

then you just need to extract the sheet tab as required

try this
=MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,LEN(CELL("filename",$A$1))-FIND("]",CELL("filename",$A$1)))

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

This formular worked.

Btw, how did you come up with that lengthy formula

Originally Posted by kelsnjoku

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

This formular worked.

Btw, how did you come up with that lengthy formula

the one that I'd prepared earlier

Originally Posted by kelsnjoku

Hello everyone,

I'm quite new to excel. I wish to know which command or formular to apply on a cell to make it assume the name of the worksheet. For instance, if the name of my worksheet is 'Joa', and I want cell A1 to take the value 'Joa', what do I do?

I also wonder if there's a way I can fill a particular cell (say A1) of all the sheets I have with a certain value. For instance, If I want all cell A1 to carry the value 100, and all B1 to carry date, how do I go about it? I'm working with over 30 sheets, and its a pain inserting the date in all sheets one after the other. A single command to execute this task would be really helpful.

Thank you for your assistance.

as for the second part of your question.

easiest way is to select all the sheets as group, ie. pick the first one and Shift-pick the last one then you can work all of them together

Originally Posted by AlanY

as for the second part of your question.

easiest way is to select all the sheets as group, ie. pick the first one and Shift-pick the last one then you can work all of them together

Thank you.

I was able to group the sheet, but couldn't edit a particular cell in all the sheets.

I prolly didn't get you...or you didn't explain well.

Originally Posted by kelsnjoku

Thank you.

I was able to group the sheet, but couldn't edit a particular cell in all the sheets.

I prolly didn't get you...or you didn't explain well.

yes, you got me there.
not quite sure your question, see attached.
isn't that what you wanted?

Book1.xlsx

Originally Posted by AlanY

yes, you got me there.
not quite sure your question, see attached.
isn't that what you wanted?

Attachment 356452

solved! :D

thank you.