Select first row (independent of filtering)

[YourBelgianDev]

So i have a spreadsheet with 2 sheets; Cases, Case Employees
A case has a casenumber and a type.



I've inserted a row above the data to display the first row of the result.
So in the screenshot that should be 08/05407.
But if the user filters it should always display the first visible case number somthing like this:



That way i can use that cell to filter on case number in the sheet Case Employees

 

[DanteAmor]

Hi and welcome to MrExcel
Try this array formula:

varios 22jul2020.xlsm
	A	B
1	Selected	08/503
2	Case	Type
5	08/503	c
6	08/504	c
7	08/505	c
10
Hoja5

Cell Formulas
Range	Formula
B1	B1	=INDEX(A3:A10,MIN(IF(SUBTOTAL(3,OFFSET(A3,ROW(A3:A10)-ROW(A3),0)),ROW(A3:A10)-ROW(A3)+1)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[YourBelgianDev]

Oh my god! I've been breaking my non-Excel brains for sooo long! thanks so much!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.