Formula - Create Sheet name in summary sheet when a new sheet is created

[Dhamo]

Hi there,


I have a workbook with summary sheet and day sheets. Daily I will add one sheet. And I need that sheet name should be added automatically to summary sheet.


If I add a sheet named '01-Feb-2013', then the sheet name should be added in "a1" of the summary sheet. And When I add another sheet('02-Feb-2013'), A2 of the summary sheet should get the update.


I will give the sheet to end user and in xlsx format. If I am not wrong, I cannot do this with Macros.


Please enlighten me.

 

[Debraj]

Hi Dhamo,


Try this...

* Create a Named Range : SheetNames

* Refers to: =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")


Now in A2 write Formula as :

=IF(ROWS($A$2:A2)<=COUNTA(SheetNames),INDEX(SheetNames,ROWS($A$2:A2) ),"")

Drag below..


PS: If you add/delete any sheet after formula entered.. You need to re-calculate the formual.


Thanks to KrishnaKumar.. for the technique..


Regards,

Deb