Self Updating / Refreshing in-cell Drop Down list of worksheet names

[LlanfairPG]

I'm looking for a way of creating an in-cell drop down list of worksheet names that updates automatically if a new sheet is created, or a sheet is renamed ?

I've looked on loads of websites, and find loads about drop downs, but none that refresh themselves.

TIA

[AliGW]

Making a list of sheet names:

1. In Names Manager on the Formulas ribbon, create a new name called SheetNames and type this into the reference box at the bottom: =GET.WORKBOOK(1)&T(NOW())
2. In A1 type this and copy down: =IFERROR(INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),ROWS(A$1:A1)),"")
3. Save as a macro-enabled workbook.

You can use this as the definition of a new named range (call it List):

=Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A)

You than use this named range (List) ofr your drop-down list reference. Sheet1 can be hidden, and as long as it's the first sheet in the workbook, you can adapt the List named range to exclude it like this:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A)

[LlanfairPG]

Thank you for all your help. You are a star. I only wish i knew as much about excel as you do.

[AliGW]

Thanks for the kind words. I have learnt almost everything I know from this forum.