vlookup and offset

[Krissy]

Hi there!So...
I have a Table_array list on one Sheet1. Group names in column G and
corresponding cities in H.

On Sheet2 I have to use a drop down list with the group names (col B) and -
ideally - should return the corresponding city in the next column (C) from
the reference list.

But I'm having trouble with making the whole thing dynamic...
Every time I add a new group with a city name I have to re-do my validations
in col B and vlookups in col C for Sheet2.

Can you give me a solution which makes the original list dynamic? And how to
add this dynamic range into a vlookup?

At the moment on Sheet2 I use (for example in C119)
=VLOOKUP(B119,Lists!$G$1:$H$15,2,FALSE)
But this only works until I don't have a new entry in row 16 on Lists sheet...

What I'd like to achieve is being able to add to my group names and cities
without needing to change the validation for col B and vlookup reference for
column C...

Anyone? I'm sure there is a short and sweet solution for this, but I'm a
beginner...

Thanks

 

[Krissy]

Ok... I managed to make the Group names dynamic - but how can I link that to
that to the next column of cities?

How can this example =VLOOKUP(B119,Lists!$G$1:$H$15,2,FALSE) be dynamic for
column H instead of stopping at H15?

Thanks again!

 

[AdamV]

Create a named range for the list you are using as the source of your lookup
and refer to that in the VLOOKUP instead. You can make the range dynamic by
using a formula for the definition rather than just a cell range reference.

In Excel <2007, go to Insert > Name > Define. In 2007 go to the Formulas
ribbon > define name button.
Give the lookup are a name such as "CitiesLookupList"
for "Refers to" enter this formula:
=OFFSET(Lists!$G$1,0,0,COUNTA(Lists!$G:$G),2)

Save this name. When you use it, you will be referring to a range which is
as long as the number of rows in G which have anything in them, and 2 columns
wide.

In Excel 2000/3 don't forget to click "Add" before closing the dialogue.
One catch is that if cells in G are the result of formulas, then "" is still
counted, even though it appear empty. You can get round this by subtracting
the result of a COUNTBLANK function if you really need to.

Now your VLOOKUP becomes more readable:
=VLOOKUP(B119,CitiesLookupList,2,FALSE)

Incidentally, you can use a similar named range as the basis for your lookup
list (eg if you are using Data Validation to create that). Just change the
"2" to a "1" in the OFFSET function so you only refer to a single column.

HTH

 

[Krissy]

Thanks Adam!

That was exactly the short and sweet and perfect solution I have been
looking for!

And it earned me a lot of credit in the office

Thanks again!