Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup and offset
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup and offset
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! "Krissy" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup and offset
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" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup and offset
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! "AdamV" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
offset within vlookup | Excel Discussion (Misc queries) | |||
VLOOKUP and OFFSET | Excel Worksheet Functions | |||
VLOOKUP with OFFSET | Excel Worksheet Functions | |||
Offset VLookup | Excel Worksheet Functions | |||
Vlookup is not enough ... can OFFSET be used ? | Excel Worksheet Functions |