Hello!

First time poster and excel novice, so might require some more explanation!

I have an excel workbook that will eventually have multiple sheets, but at the moment only has two.

I have used a VBA code I found online (couldn't explain it!) to generate a list of worksheets.

On my summary sheet I have a list of names in Column B, and I would like to do a 3D sumif where in each worksheet column B is considered for that name, and the number is column I is returned. I can do this by using the formula;

=SUMPRODUCT((SUMIF((INDIRECT("'"&Worksheets&"'!$B:$B")),B4,(INDIRECT("'"&Worksheets&"'!$I:$I")))),0)

where B4 refers to the name I'm searching for in this particular case.

I intend on allowing up to 50 sheets in this workbook, therefore, I have allowed for 50 names in my range called 'Worksheets'. The problem is that the majority of the rows in the range are empty, and therefore the formula above returns a #REF error. The formula only works when all 50 sheets are generated and therefore there are no blank cells in the range.

Is there any (novice friendly ) way around this? is there a way of generating a dynamic range that grows as the list grows? or a way of getting the formula to exclude blank rows in the range?

Thanks in advance for the post, and sorry if this has been posted in the wrong place/posted before.

Thanks