Formula continues to count up

[Glasgowsmile]

Good day,

I've got this formula below that I'm having issues with.

=IF(AND($A50<>"", $A$5>34),$A50+1,"")

Essentially $A$5 starts at >1 in Cell A17 and in Cell A18, I changed it to 2 - it continues like this up to $A$5>34 like you see above but I've had to do that manually because when I tried to drag the pattern down it wouldn't continue to count up. Is there a way to do that? I'm now having to drag this formula down several hundred sells so it would be $A$5>344 but I don't want to have to manually go through and update all of these.

 

[Eric W]

Try this in A5:

=IF(AND($A50<>"", $A$5>ROWS($A$5:$A5)),$A50+1,"")

 

[Glasgowsmile]

Try this in A5:

=IF(AND($A50<>"", $A$5>ROWS($A$5:$A5)),$A50+1,"")

This worked wonderfully if you would - could you explain a little about how it works? I can't quite make sense of it.

 

[Eric W]

Sure. ROWS is a function that just tells you how many rows are in the range. So when you start in the A5 cell, the range is $A$5:$A5, so it has 1 row in it. When you drag the formula down a row, that range changes to $A$5:$A6, since the last row reference doesn't have the $ to make it absolute. The $A$5:$A6 range has 2 rows, so ROWS gives 2 now. As you drag it down, the range gets bigger by 1 row every time.

Hope this helps! ?