reference cell from advanced filter

  • Thread starter Thread starter Nathan
  • Start date Start date
N

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?
 
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))
 
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
 
Back
Top