Nested Indirects

**Kay9835** · 05-03-2020, 05:31 PM

I have this formula in a sheet:

=IF($A8<>"",SUMPRODUCT(COUNTIF(INDIRECT("'"&$K$10:$K$33&"'!"&CELL("address",$B9)),B$3)),"")

where K10 thru Knn contains a list of the sheet names in the workbook. The number of sheets is variable, so I have calculated the last row with a valid sheet name in cell L58.

I want to replace the 33 in the reference $K$33 with the value in L58. I think I can do this with another INDIRECT, or possibly an OFFSET, but in either case I can't figure out the exact syntax. Help would be much appreciated, as I'm now so frustrated I'm about to throw something at my monitor.

**Bob Phillips** · 05-03-2020, 05:50 PM

Isn't it just as simple as

=IF($A8<>"",SUMPRODUCT(COUNTIF(INDIRECT("'"&INDIRECT("$K$10:$K$"&L8)&"'!"&CELL("address",$B9)),B$3)),"")

**Kay9835** · 05-03-2020, 05:58 PM

Not quite, though that's probably closer than I've come. Your version throws a #REF error. I think the problem is (at least one) missing quote somewhere between L8 and "'!, but I can't quite wrap my head around it yet. I'm playing with it now, though.

**Kay9835** · 05-03-2020, 06:02 PM

Ah, got it. The reference needed to be to L58, you typed L8, and I simply used that. Thanks very much!!!!!

**Kay9835** · 05-03-2020, 07:15 PM

To be very precise about it, the completely correct version is:

=IF($A8<>"",SUMPRODUCT(COUNTIF(INDIRECT("'"&INDIRECT("$K$10:$K$"&$L$58)&"'!"&CELL("address",$B9)),B$3)),"")

The $ around the cell containing the last row of the list of sheets (i.e, $L$58 vs L58) are important - at least to me, as I'm copying this formula to multiple columns and rows, and that reference needs to stay static.