Formula which reads specific Sheet names

[Mr2017]

Hi

Is it possible to create a formula which reads subsequent Sheet names?

So if I had data in Sheet 1, for example, I'd like

cell A1 to read the name of Sheet 2 (which would be called 'Chocolate')

cell A2 to read the name of Sheet 3 (which would be called 'Crisps')

I know you can use this to get the current name of the current worksheet.

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

But I was wondering if it's possible to get the names of all other sheets in the current worksheet?

TIA

 

[Eric W]

This thread shows 2 ways to get the sheet names:

https://www.mrexcel.com/forum/excel-questions/932863-worksheet-names-cells.html

One uses VBA, one use a trick using the Name Manager, both require the workbook to be macro enabled. If you use a combination of the Name Manager trick and the CELL("filename" formula to get the current sheet, you can get the next worksheet's name.

 

[Mr2017]

Ok, thanks Eric.

I also found another solution, which was to change the reference to cell A1 in this formula, to a cell reference in the sheet that you want to get the name of.

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

Thanks again for posting your solution as well.