reference cell from advanced filter

[Nathan]

I'm sure this is slam dunk, but I can't figure it out

On worksheet 1, I have executed an Advanced filter of 900 rows, to get down
to one row. No problem.

Now, on Worksheet 2, I want to reference an ID number from my filtered row.
Obviously, simply doing a regular cell reference won't work (=Sheet1!G271),
because the filtered row will NOT always be row 271. So, how the heck do I
reference the filtered cell without going back and linking to it new each
time?

 

[Jacob Skaria]

The below formula will return the 1st entry in Column G from the filtered
data in Sheet1.

=INDEX(Sheet1!G2:G1000,MATCH(TRUE,INDEX(SUBTOTAL(3,
OFFSET(Sheet1!G2:G1000,ROW(Sheet1!G2:G1000)-
MIN(ROW(Sheet1!G2:G1000)),0,1))>0,0),0))

 

[Nathan]

OUTSTANDING! Worked like a charm.

Thank you so much!

 

[Ashish Mathur]

Hi,

You may transfer the output of advanced filter directly to another worksheet
using Copy to another location. Click on any cell in the destinatino
worksheets and then run advanced filters