Return row number of blank row

[straubj]

I have a set of spreadsheets and each one has a blank line somewhere in the middle area. I need a formula that I can place at the bottom of each spreadsheet that returns the row number of the blank row. Alternatively, finding one blank cell and returning its row number would work as well if there is a way to do that. All help is very appreciated!

 

[Rick Rothstein]

Assuming for your alternate that Column A is the column that is filled with data except for the blank cell you want to find. Let's say the last data cell in Column A is located at Row 20, then use this formula to find the row number with the blank cell...

=SUMPRODUCT((A1:A20="")*ROW(A1:A20))

 

[straubj]

Found a solution

=SMALL(IF($A$1:$A$1080="",ROW(INDIRECT("1:"&ROWS($A$1:$A$1080))),""),ROW(1:1))

Thanks!

 

[Aladin Akyurek]

I have a set of spreadsheets and each one has a blank line somewhere in the middle area. I need a formula that I can place at the bottom of each spreadsheet that returns the row number of the blank row. Alternatively, finding one blank cell and returning its row number would work as well if there is a way to do that. All help is very appreciated!

Control+shift+enter, not just enter:

=MATCH(TRUE,ISBLANK(A2:A100),0)+ROW(A2)-1

=MATCH(TRUE,A2:A100="",0)

Native row numbers:

Control+shift+enter, not just enter...

=MATCH(TRUE,ISBLANK(A2:A100),0)+ROW(A2)-1

=MATCH(TRUE,A2:A100="",0)+ROW(A2)-1

 

[Rick Rothstein]

Found a solution

=SMALL(IF($A$1:$A$1080="",ROW(INDIRECT("1:"&ROWS($A$1:$A$1080))),""),ROW(1:1))

SMALL? You needed to use the SMALL function? Why, because you have more than one blank row? Does that mean you misled us? Everything about your first post said you only had a single blank row. Here is what you said (I highlighted the relevant parts)...

I have a set of spreadsheets and each one has a blank line somewhere in the middle area. I need a formula that I can place at the bottom of each spreadsheet that returns the row number of the blank row. Alternatively, finding one blank cell and returning its row number would workas well if there is a way to do that. All help is very appreciated!

 

[straubj]

everything I said is true. there is only one blank line. My friend helped me with that formula. To be honest, I have no idea why it works, but it does.

 

[Rick Rothstein]

everything I said is true. there is only one blank line. My friend helped me with that formula. To be honest, I have no idea why it works, but it does.

Okay, let me ask then... did you try any of the shorter, non-volatile formulas that have been offered to you in this thread in response to the question you asked here?