Return blank cell after filtering data

[jk2022]

I'm using this formula to return the top row's value after being filtered.

=INDEX(CY6:CY100,MIN(IF(SUBTOTAL(3,OFFSET(CY6,ROW(C6:CY100)-ROW(CY6),0)),ROW(CY6:CY100)-ROW(CY6)+1)))

If the row that comes up after being filtered has a blank in the cell I'm referencing, then the formula will return whatever value was in the first row before any filters were applied. I would like the formula to return a blank cell if nothing is in the referenced cell.

Any help greatly appreciated.

 

[Peter_SSs]

Welcome to the MrExcel board!

Your description is not matching my experience. Could we have a small (5-10 rows) set of unfiltered sample data from column CY (preferably with XL2BB) and explain in relation to the sample data which cells are hidden after the filter and what result you then want.

Can you also confirm whether the values in column CY ..
- are text, numerical, some of each, something else?
- are the result of formulas or just fixed values?