Need formula to reference the first visible cell in a filtered column, please.

[Odie]

Apologies, I have looked all over for this and cannot find. My range is B15:Z500. Filter column is K. In cell K9 I need a formula to always show the first value of the of the first VISIBLE cell in column K. Please advise me how to do this. Excel 2003 Thanks, Odie.

 

[pgc01]

Hi Odie

Assuming the first visible cell in the range has a value, try in K9:

=INDEX(K:K,MIN(IF(SUBTOTAL(3,OFFSET(K15,ROW(K15:K500)-ROW(K15),0)),ROW(K15:K500))))

This in an array formula and so MUST be confirmed with CTRL+SHIFT+ENTER and not just ENTER.

 

[pgc01]

Sorry, that formula works in excel 2010, but in prior versions of excel this one will surely work:

=INDEX(K15:K500,MIN(IF(SUBTOTAL(3,OFFSET(K15,ROW(K15:K500)-ROW(K15),0)),ROW(K15:K500)-ROW(K15)+1)))

This in an array formula and so MUST be confirmed with CTRL+SHIFT+ENTER and not just ENTER.

 

[Odie]

Works, thank you very much. Odie

 

[pgc01]

You're welcome. Thanks for the feedback.

 

[nishantbajpai]

Hi,have similar query.But the above formula does not work in my sheet.

Column A has 20 values.Applying filter in column A with given formula in cell B1.

=INDEX(A2:A20,MIN(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A20)-ROW(A2),0)),ROW(A2:A20)-ROW(A2)+1)))

But its not giving desied result.Pl guide.

 

[pgc01]

Hi
Welcome to the board

This in an array formula, did you confirm it with CTRL+SHIFT+ENTER?

 

[nishantbajpai]

Hi, its now resolved.My little mistake..

Thanks for attending.

 

[pgc01]

I'm glad it's OK now. Thanks for the feedback.

 

[dataguy84]

I have been looking for a similar formula, and I wonder if you can help. I have a table with office names in B36:B151. This table, once sorted, populates a chart with these offices' ratings etc. Well, I would like an array that would return the location name from the top of the sorted data in column B to display in cell B2. B2 will act as a header or title when the document/chart is printed, etc.

The entire table range is is B36:I151 but i only need to return the first visible cell from data sorted in B36:B151.

Sorry, that formula works in excel 2010, but in prior versions of excel this one will surely work:

=INDEX(K15:K500,MIN(IF(SUBTOTAL(3,OFFSET(K15,ROW(K15:K500)-ROW(K15),0)),ROW(K15:K500)-ROW(K15)+1)))

This in an array formula and so MUST be confirmed with CTRL+SHIFT+ENTER and not just ENTER.