Sum Hlookup results from multiple tabs with dynamic range

Hi, I have a summary page on which I would like to use an hlookup to find the figures on multiple tabs (dependent on date), the number of tabs is changing (dynamic).

Instead of incorporating hlookup, I have tried using =SUMPRODUCT(SUMIF(INDIRECT("'"&TabNames&"'!$C160:$Z160"),DD$7,INDIRECT("'"&TabNames&"'!$C171:$Z171"))) which I found on another thread, with no luck.

Any ideas?

Thanks

Have you created a named range that refers to the tabs that the formula should look at, and called it tabnames?

If not then you would need to do so for the formula to work correctly. It may require more work, but one thing at a time.

Yes I believe I have created the named range correctly. Example attached, hopefully!

Thanks in advance.

Not quite correctly.

Your named range includes blank cells which cause the error in the formula. Change the named range to refer to Lists!$A$2:$A$5 and it will work correctly.

Alternatively, if you need the range to allow for future additional sheets.

=Lists!$A$2:INDEX(Lists!$A:$A,MATCH("zzz",Lists!$A:$A))

Which will make the range dynamic from A2 to the last entry. ** this will fail if you add anything else other than a sheet name in Lists column A, even if there is a big gap. **

Hi Jason, thanks, have edited the name to the above. Still do not have a working formula tho.

Does it matter if that list of sheets is populated via a button using some vba?

Thanks

No, it should still work. I've tried both methods in your sample file, then added some more sheets and clicked the button, the formula still works.

Thank-you very much.