I have a sheet which has some results I need. This sheet is then duplicated and renamed and the results updated. On a separate sheet I have a list of numbers, each sheet is renamed as a number from this column. I then have a formula which pulls a cell from each sheet based on their name using the number column. I am using the indirect formula =INDIRECT("'"&A5&"'!"&"B3" also =INDIRECT(A6&"!B3") seems to work as well. The problem I have is that when I duplicate a sheet and rename it the formula does not seem to work. Interestingly when you are in the cell the result is shown in blue above but there is an error reported.

If I drag the formula down after I have duplicated the sheet it works. It just does not seem to recognise a new sheet.

Here is a very simple example of what I am trying to achieve.

Many thanks for any advice and help.

you can also use:

=IF(NOW()=NOW(), INDIRECT("'"&A14&"'!"&"B3"), )

and set minute updates: