Filtering a dynamic range

[Guest]

Hiya,

I have a dynamic range set up (named range) - this is 8 columns across and
loads (varying) of rows down. Lets call it DATA.

For a vlookup, however, I only want to look at columns 3-7.

Is there anyway that I can filter the existing dynamic range rather than
creating a new one?

PS I know it would make no difference if I have 3-8 in the range, but I want
to learn how to do filter at both ends for something else.

Thanks guys!

B

 

[Guest]

I do not understand your question. please give me your definition of filtering

 

[Bob Phillips]

Maybe something like

=VLOOKUP("some_value",OFFSET(dynarange,2,0),2,FALSE)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Instead of the range looking at Columns A to H, I want to look at columns B
to G.

 

[Guest]

If you want to use column B for the lookup range in a vlookup, just set your
formula as =vlookup(lookupvalue,B:G,column numberusing B as 1, 0)
if you want to extract anything from column A you can use index-match
formulas.

 

[Roger Govier]

Hi Basil

One way, create a new named range, Data2
=INDEX(data,1,3):INDEX(data,COUNTA(Sheet3!C:C),7)

this will give you columns C:G for as many rows as you have in column C
of your table.
If there is another column which always has values, whereas other
columns may be blank, then carry out the COUNTA() function on that
column, even if it is outside the range of the columns you want to look
at.

You could hold the variables, for first column 3, and last column 7 in
cells, and substitute those cells in the formula.
That way, you could re-define Data2 just by altering those cell's
values.