Cell Reference of the nth Non-Blank cell in a column

[kevsvette]

I need a formula that will return the CELL reference (not the value in the cell) of the Nth non-blank cell. This is so I can exclude the column header. So really I need the 2nd non-blank cell, but thought it might be helpful to add the flexibility.

So basically if he first non-blank that could have text/date/number. I've scoured the web and cant find what I'm looking for.

 

[AhoyNC]

Maybe something like this:
If you have Excel 2010 or later the formula in C3 below. If not then the formula in C5 that must be entered with CTRL-SHIFT-ENTER.
In C2 of the example enter the nth blank cell you want.
Change ranges to match your data.

Excel Workbook
	A	B	C
1	Title
2		nth row =	2
3	T1		$A$3
4	T2
5	T3		$A$3
Sheet

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=CELL("address",INDEX($A$2:$A$7,SMALL(IF($A$2:$A$7<>"",ROW($A$2:$A$7)-ROW(INDEX($A$2:$A$7,1,1))+1),2)))

 

[Peter_SSs]

I need a formula that will return the CELL reference (not the value in the cell) of the Nth non-blank cell.

Just wondering what you are going to then do with that cell reference?
Reason for asking is that it may be possible to bypass the cell reference and go straight to the next action.

 

[kevsvette]

I use it for other formula as range references... I have found a means to find the CELL address of the last non-blank cell, but couldn't figure out the first (minus the header) non blank cell. This information is then used within other formulas via an Indirect formula as a range reference.

 

[Aladin Akyurek]

I use it for other formula as range references... I have found a means to find the CELL address of the last non-blank cell, but couldn't figure out the first (minus the header) non blank cell. This information is then used within other formulas via an Indirect formula as a range reference.

How do you mean? Care to elaborate on what you need?

 

[kevsvette]

Aladin... you formula works perfectly Thank You!!!

So to explain its use... I use your formulas result in other Countifs formulas as the first cell reference in a range.

I had already figured out how to find the last non blank cell in the column so I use these two cell addresses as the countifs range.

 

[Aladin Akyurek]

Aladin... you formula works perfectly Thank You!!!

So to explain its use... I use your formulas result in other Countifs formulas as the first cell reference in a range.

I had already figured out how to find the last non blank cell in the column so I use these two cell addresses as the countifs range.

That's fine. Thanks for the update.

It looks like you have:

COUNTIFS(INDIRECT(firstnonblankcellref:lastnonblankcellref),criterion)

If A1 is a header (not data) and A2:A7 consists of numeric data, we can also have: Control+shift+enter, not just enter:

=COUNTIFS(INDEX($A$2:$A$7,MIN(IF($A$2:$A$7<>"",ROW($A$2:$A$7)-ROW(INDEX($A$2:$A$7,1,1))+1))):INDEX($A$2:$A$7,MATCH(9.99E+307,$A$2:$A$7)),criterion)

If A2:A7 consists of text, replace in the above formula 9.99E+307 with REPT("z",255).