cell equal only filtered cell

[Sheripres]

Hello:

In column A, I have 1,299 unique part numbers. The end user is going to choose one through a filter. I need cell B1 to equal that filtered number.

I need this because I am doing an Indirect formula that needs just the chosen (filtered) part number, but because the filtered number is on a different row each time, I cannot equal that cell.

Thank you,

Sheri

 

[Sheripres]

Found it!

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

Hope this helps others! I was looking for over an hour and finally found the solution.