Extracting Sheet Names

[engrnoir]

Hi. I'm a fairly new member here so I don't know how things work. But I wish to extract the sheet names from my workbook. I tried the formula I found online which is :
Define Name > =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")
It worked for me at first but it made me save it as a Macro-enabled Workbook. I tried re-opening it and upon opening, it now returned a "#BLOCKED!" error. For context, my file is in my Onedrive (Desktop) and I'm working on the file while its inside the Onedrive.
I tried watching videos online where they used the same formula but they weren't asked/given a prompt to save it as a Macro-enabled Workbook. I've had this same scenario in a different project but I did not get this #BLOCKED! error. How do I fix this?

 

[Georgiboy]

I believe that GET.WORKBOOK is an old Excel function and needs the workbook to be saved as 'xlsm'

Are the sheet names to be returned numbered, i.e. 'Week 1', 'Week 2' etc..?

 

[engrnoir]

I believe that GET.WORKBOOK is an old Excel function and needs the workbook to be saved as 'xlsm'

Are the sheet names to be returned numbered, i.e. 'Week 1', 'Week 2' etc..?

Not really. It's varying sheet names. Anyway, I, fortunately, got an answer somewhere else. I was probably trying to take on a problem a different way for the same result when I could've done so more directly.
I actually just wanted it to return the current sheet's name in a cell so I can reference my sheet name for future different items and I got my answer from ChatGPT/AI Excel Bot or something like that. I read up above that it is banned but this one did work and I did counter check its structure.
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
This one returns the current sheet's name. I can put it anywhere on the sheet (even in its own reference). I didn't know cell can reference the literal "filename" like that. I found this on google before but I thought I would need to change those inside the quotation marks with what I have so I thought this wasn't dynamic.
Anyway, I hope this would be reference by other people in the future. How do I close this thread now? Haha.

 

[Georgiboy]

Glad you got it sorted, I personally have nothing against ChatGPT but i can see why the forum would be against its use when providing solutions. It is very good at producing code that looks like it will work but does not.

 

[Dave Patton]

With Excel 365, you could use the following which will show the sheet name for the current sheet.

=TEXTAFTER(CELL("filename",$A$1),"]")

Check Excel's help for the Cell function.

 

[engrnoir]

With Excel 365, you could use the following which will show the sheet name for the current sheet.

=TEXTAFTER(CELL("filename",$A$1),"]")

Check Excel's help for the Cell function.

Yeah, this one works too. Thanks for giving another alternative!