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.

 

[truse]

I had a similar issue I resolved in a slightly different way.
I added an index column to my data with =row().
Then I used index and subtotal to find the first visible record.
If my index column is A and the result I want to return is in B the formula is like this.
=index(B:B,subtotal(5,A:A))

 

[Jrider1]

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.

how can i make the cell blank if no filter is apllied? this works properly, just need to make the cell blank if there are no filters

 

[lazyhacker]

I thought I was starting with this same problem, but realized eventually what I really needed was the following:
{=TRIMMEAN(IF(SUBTOTAL(3,OFFSET(OP_CODE_TIMES[RATE],ROW(OP_CODE_TIMES[RATE])-ROW(T33),,1)),OP_CODE_TIMES[RATE]),B4)}

Hope it helps someone....