Prev: Removing ''VALUES''
Next: Print Preview Different than What I see in the Excel File
From: Nathan on 25 Mar 2010 10:18
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?
From: Jacob Skaria on 25 Mar 2010 10:33
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?
From: Nathan on 25 Mar 2010 10:43
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?
From: Ashish Mathur on 25 Mar 2010 22:02
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" <Nathan(a)discussions.microsoft.com> wrote in message
news:1517ACF2-C5A6-466F-9F73-C4E8694D03BB(a)microsoft.com...
> 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?

 | 
Pages: 1
Prev: Removing ''VALUES''
Next: Print Preview Different than What I see in the Excel File