Multiple Worksheet Tab Summary Sheet Problems

Attempting to create a formula sheet that will bring in data from many other worksheet tabs. This will be different amounts for separate reports, all into one summary sheet for each report.

I need some way of:

1 - Automating a run of worksheet references, e.g. "Worksheet 1", "Worksheet 2" for each row. I can do this for copying across for separate category within one worksheet reference but I have not found a way to automate this for more than one worksheet yet.

2 - If the worksheet reference name is not within the file, it will prompt me to Update Values. I just want it to remain blank and not prompt me (this is because there will be many blank cells in most reports and I do not want other colleagues running these reports having to click cancel for each and every blank cell that is prompting for updated values).

Currently using formula - "IF(ISNA(VLOOKUP(B$1, 'Household Number 1'!$A:$D, 2, FALSE)), "", VLOOKUP(B$1, 'Household Number 1'!$A:$D, 2, FALSE))"

Any help here would be superb! Otherwise this is going to be a very long week for me editing worksheet reference names and also a very clunky report summary each time it is recreated.

Thanks!

G

Just made a quick little example, just the 3 tabs made but I think it gives across the problem. I've also printscreen the prompt that it shows when there is a missing worksheet reference within the formula.