Sum same cell across multiple worksheets using Indirect

Question

My workbook has a couple of hundred worksheets with names running from 2200 to 16000. Each worksheet has calculations in Column Q and each row has the total for an ingredient for that particular worksheet. I'm trying to sum all the ingredient totals from the 200 worksheets onto a summary worksheet titled RM Usage.

=SUM('2200:16000'!$Q100) gives me the number for all the worksheets in Q100 (which gives me the total for the ingredient number on Row 100 for all the worksheet pages).

I need to create a table on RM Usage that gives me the number for each individual worksheet.

I've created a table with the number for each worksheet in row 1, and the hundreds of ingredients in Column A. Column I lists the $Q$? reference to plug into the formula for the ingredient in that row.

I'm trying things like this but they're not working. What am I missing?

=SUMIF('2200:16000'!,J$1,INDIRECT(I4)) to get the total for the ingredient in row 4 that is found in the page number showing in J1. But this formula returns all zeros.

Answer 1

Turns out it was much simpler than I thought. Since I already had the title of each worksheet in row 4 this formula works.

=INDIRECT(CONCATENATE("'"&J$4&"'!",$I5))