Can't get Indirect range to autofill

**kwarden01** · 05-07-2013, 10:11 AM

I am working on a spreadsheet that tracks backorders monthly. The workbook has 13 sheets - one for each month and a graph sheet that utilizes a drop down box to select the month of data to display in the graph. The drop down box is the reason for the INDIRECT function. Rather than reference a sheet directly within all the forumlas, the sheet names are all in the drop down and I indirectly reference the cell with the drop downs ($A$19).

Now the issue I have is within a COUNTIF formula, I indirectly reference a incremental range, but I cannot get the formula to autofill correctly. The range references remain absolute. Here is the formula that I need help with (I made the problem area bold):

=IF(COUNTIF(INDIRECT($A$19&"!M2:$M$1048576"),INDIRECT("'"&$A$19&"'"&CELL("address",M2)))=1,INDIRECT("'"&$A$19&"'!"&CELL("address",M51)),"")

The M2's in the formula above should all incrementally increase (M3, M4, etc) when I autofill. The CELL references autofill fine, but the M2 in the range does not.

I looked all over the web and was not able to figure this one out. Thanks in advance for your help!

**Jonmo1** · 05-07-2013, 10:16 AM

I assume you mean this M2
=IF(COUNTIF(INDIRECT($A$19&"!M2:$M$1048576"),INDIRECT("'"&$A$19&"'"&CELL("address",M2)))=1,INDIRECT("'"&$A$19&"'!"&CELL("address",M51)),"")

That's part of a text string "!M2:$M$1048576", this is why it's not incrimenting.

You need to apply the Cell("Address",M2) function to that part as well

=IF(COUNTIF(INDIRECT($A$19&"!"&CELL("Address",M2)&":$M$1048576"),INDIRECT("'"&$A$19&"'"&CELL("address",M2)))=1,INDIRECT("'"&$A$19&"'!"&CELL("address",M51)),"")

**kwarden01** · 05-07-2013, 10:58 AM

That works perfectly! Thank you so much! I assumed it was something like that, but for the life of me I could not get that range to work with the CELL("address") command. I was trying to put the $M$1048576 in the parentheses and it would just give me errors. I am still trying to get used to placement of all the quotation marks and parentheses.

Thanks again!