cell equal only filtered cell

Sheripres

Board Regular
Joined
Sep 7, 2011
Messages
77
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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Sheripres

Board Regular
Joined
Sep 7, 2011
Messages
77
Found it!

Code:
=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,096,245
Messages
5,449,225
Members
405,557
Latest member
AJTatsuo

This Week's Hot Topics

Top