Copy Cells Down from Different Tab Names in Order

See if this approach will help, it relies on having a UDF (User Defined Function) to pull in existing (and new) sheet names, which can then be references by the INDIRECT()

1. create a named name, and call it SheetNames
2. in the Refers To, enter this...
=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")
3. Put this where you want the sheet names to be (I used column A and moved your stuff over 1 column)
=IFERROR(HYPERLINK("#"&"'"&INDEX(Sheetnames,ROWS($A$2:A2))&"'!A1",INDEX(Sheetnames,ROWS($A$2:A2))),"")
copy down as needed
4. change your formulas to reference column A instead of hard coding the sheet name...
=IFERROR(INDIRECT("'"&A3&"'!b5"),"")

See how you get on with that?

Hi FDibbins,
It always amazes me how smart you guys on here are! It took me some googling on how to create the named names etc but seemed to have figured it out and it works perfectly!
Thanks for your help!

Happy to help, thanks for the kind words and the feedback

Sorry I did not include how to create a named range, I guess sometimes we just assume stuff