Hi All

i have a list of records in the range A10:K500 ... the value in column D
determines the values in columns J & K. Column D has 200 options. What i
want to do is use autofilter to filter the records on column D but then not
print columns J & K rather have the value from the first record in J
displayed in cell B3 and the value from the first record in K displayed in
B4, and then set my print range as A1:I500.

As we're dealing with filtered / visible cells here i can't figure out a way
to do it ... any ideas?

--
Cheers
JulieD

Hi Julie!

I'm not sure if this is what you're looking for, but try the following...

B3:

=INDEX(J10:J500,MATCH(TRUE,SUBTOTAL(3,OFFSET(D10:D 500,ROW(D10:D500)-MIN(R
OW(D10:D500)),0,1))0,0))

B4:

=INDEX(K10:K500,MATCH(TRUE,SUBTOTAL(3,OFFSET(D10:D 500,ROW(D10:D500)-MIN(R
OW(D10:D500)),0,1))0,0))

Both these formulas need to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
"JulieD" wrote:

Hi All

i have a list of records in the range A10:K500 ... the value in column D
determines the values in columns J & K. Column D has 200 options. What i
want to do is use autofilter to filter the records on column D but then not
print columns J & K rather have the value from the first record in J
displayed in cell B3 and the value from the first record in K displayed in
B4, and then set my print range as A1:I500.

As we're dealing with filtered / visible cells here i can't figure out a way
to do it ... any ideas?

Hi Dominic

thanks, looks good, i'll test it out tomorrow.
--
Cheers
JulieD

"Domenic" wrote in message
...
Hi Julie!

I'm not sure if this is what you're looking for, but try the following...

B3:

=INDEX(J10:J500,MATCH(TRUE,SUBTOTAL(3,OFFSET(D10:D 500,ROW(D10:D500)-MIN(R
OW(D10:D500)),0,1))0,0))

B4:

=INDEX(K10:K500,MATCH(TRUE,SUBTOTAL(3,OFFSET(D10:D 500,ROW(D10:D500)-MIN(R
OW(D10:D500)),0,1))0,0))

Both these formulas need to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
"JulieD" wrote:

Hi All

i have a list of records in the range A10:K500 ... the value in column D
determines the values in columns J & K. Column D has 200 options. What
i
want to do is use autofilter to filter the records on column D but then
not
print columns J & K rather have the value from the first record in J
displayed in cell B3 and the value from the first record in K displayed
in
B4, and then set my print range as A1:I500.

As we're dealing with filtered / visible cells here i can't figure out a
way
to do it ... any ideas?

Hi Dominic

WORKS GREAT - thanks so much

--
Cheers
JulieD

"JulieD" wrote in message
...
Hi Dominic

thanks, looks good, i'll test it out tomorrow.
--
Cheers
JulieD

"Domenic" wrote in message
...
Hi Julie!

I'm not sure if this is what you're looking for, but try the following...

B3:

=INDEX(J10:J500,MATCH(TRUE,SUBTOTAL(3,OFFSET(D10:D 500,ROW(D10:D500)-MIN(R
OW(D10:D500)),0,1))0,0))

B4:

=INDEX(K10:K500,MATCH(TRUE,SUBTOTAL(3,OFFSET(D10:D 500,ROW(D10:D500)-MIN(R
OW(D10:D500)),0,1))0,0))

Both these formulas need to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
"JulieD" wrote:

Hi All

i have a list of records in the range A10:K500 ... the value in column D
determines the values in columns J & K. Column D has 200 options. What
i
want to do is use autofilter to filter the records on column D but then
not
print columns J & K rather have the value from the first record in J
displayed in cell B3 and the value from the first record in K displayed
in
B4, and then set my print range as A1:I500.

As we're dealing with filtered / visible cells here i can't figure out a
way
to do it ... any ideas?