If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
reference cell from advanced filter
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? |
#2
|
|||
|
|||
reference cell from advanced filter
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)) -- Jacob "Nathan" wrote: 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? |
#3
|
|||
|
|||
reference cell from advanced filter
OUTSTANDING! Worked like a charm.
Thank you so much! "Jacob Skaria" wrote: 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)) -- Jacob "Nathan" wrote: 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? |
#4
|
|||
|
|||
reference cell from advanced filter
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 -- Regards, Ashish Mathur Microsoft Excel MVP "Nathan" wrote in message ... 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? |
Thread Tools | |
Display Modes | |
|
|