Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
VLOOKUP and Multi Lists
Subject: VLOOKUP and Multi Lists
need help with this dropdown multi list with vlookup. B1 is the dropdown list (on sheet named Data) that holds 4 lists (ad, el, mr, sz) this are lists that hold the codes for group names (TB =The Beatles BD= Bob Dylan..so on) each list has different number of rows. I have Colum A on the Main sheet linked to the Above drop down, so that if AD is selected in B1 then in A5 the dropdown list is AD and if B1 is MR then A5 drop down is changed to MR. this part works good. Now the problem, I want colum C to show the Group name depending on what colum A has in it.(I have the List setup and can get it to work (sort of) I have 2 ways to do this now. The first works but only if the formula is changed to match the colum A selection. This takes 4 different formulas 1. =VLOOKUP(A5,ad:Data!E2,5,FALSE) 2. =VLOOKUP(A5,el:Data!F2,5,FALSE) 3. =VLOOKUP(A5,mr:Data!G2,5,FALSE) 4. =VLOOKUP(A5,sz:Data!H2,5,FALSE) but it is to much work to put in different formulas if 1 would do the trick.So I changed it to look like this: 1. =IF($B$1="ad",(VLOOKUP(A5,ad:Data!E2,5,FALSE)),IF( $B$1="el",VLOOKUP (A5,el:Data!F2,5,FALSE))) 2. =IF($B$1="mr",(VLOOKUP(A5,mr:Data!G2,5,FALSE)),IF( $B$1="sz",VLOOKUP (A5,sz:Data!H2,5,FALSE))) now it takes 2 to do this the best I could do was have 3 lists in 1 of the above formulas (I can always change the lists to be 3 insted of 4 so this is not the issue) Heres the problem: If A6 is TB then C6 is The Beatles. but if I change the List in B1 to say EL so I can select EP in A7 so Elvis Prestly shows up in C7 : then the Text in C6 changes to false. What I am looking for is what can I do to keep the last selection from changing every time I add new selection in a cell. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
VLOOKUP and Multi Lists
Hi,
I don't know if I have understood correctly, but I have faced similar situations and this an approach that worked for me: 1. Name the ranges where the list values are, including all the rows and columns that will be searched using VLOOKUP (can be the same as you have now, TB, EL, SZ, MR) 2. In the cell $B$1, or wherever you are selecting the code, make the dropdown with the names that you have created in point 1 3. Use the INDIRECT in the VLOOKUP formula, something like this: =VLOOKUP(A5,INDIRECT($B$1),5,FALSE) The formula will search the named range that is represented in that cell. Hope this helps, Miguel. "dpatte601" wrote: Subject: VLOOKUP and Multi Lists need help with this dropdown multi list with vlookup. B1 is the dropdown list (on sheet named Data) that holds 4 lists (ad, el, mr, sz) this are lists that hold the codes for group names (TB =The Beatles BD= Bob Dylan..so on) each list has different number of rows. I have Colum A on the Main sheet linked to the Above drop down, so that if AD is selected in B1 then in A5 the dropdown list is AD and if B1 is MR then A5 drop down is changed to MR. this part works good. Now the problem, I want colum C to show the Group name depending on what colum A has in it.(I have the List setup and can get it to work (sort of) I have 2 ways to do this now. The first works but only if the formula is changed to match the colum A selection. This takes 4 different formulas 1. =VLOOKUP(A5,ad:Data!E2,5,FALSE) 2. =VLOOKUP(A5,el:Data!F2,5,FALSE) 3. =VLOOKUP(A5,mr:Data!G2,5,FALSE) 4. =VLOOKUP(A5,sz:Data!H2,5,FALSE) but it is to much work to put in different formulas if 1 would do the trick.So I changed it to look like this: 1. =IF($B$1="ad",(VLOOKUP(A5,ad:Data!E2,5,FALSE)),IF( $B$1="el",VLOOKUP (A5,el:Data!F2,5,FALSE))) 2. =IF($B$1="mr",(VLOOKUP(A5,mr:Data!G2,5,FALSE)),IF( $B$1="sz",VLOOKUP (A5,sz:Data!H2,5,FALSE))) now it takes 2 to do this the best I could do was have 3 lists in 1 of the above formulas (I can always change the lists to be 3 insted of 4 so this is not the issue) Heres the problem: If A6 is TB then C6 is The Beatles. but if I change the List in B1 to say EL so I can select EP in A7 so Elvis Prestly shows up in C7 : then the Text in C6 changes to false. What I am looking for is what can I do to keep the last selection from changing every time I add new selection in a cell. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
VLOOKUP and Multi Lists
Here is the worksheet , Maybe you can understand what i want if you see it
at work http://www.savefile.com/files/8153879 Hi, I don't know if I have understood correctly, but I have faced similar situations and this an approach that worked for me: 1. Name the ranges where the list values are, including all the rows and columns that will be searched using VLOOKUP (can be the same as you have now, TB, EL, SZ, MR) 2. In the cell $B$1, or wherever you are selecting the code, make the dropdown with the names that you have created in point 1 3. Use the INDIRECT in the VLOOKUP formula, something like this: =VLOOKUP(A5,INDIRECT($B$1),5,FALSE) The formula will search the named range that is represented in that cell. Hope this helps, Miguel. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
VLOOKUP and Multi Lists
I have looked at it, and the approach I proposed earlier wouldn't work here,
you are right. My proposal for the note in B15 is changing the formulas in C15:C18, to avoid checking for the value in B1, but directly for the ranges. Something like: C15: =IF(ISNA(VLOOKUP(A15,week1:Data!E1,5,FALSE)),VLOOK UP(A15,el:Data!F15,5,FALSE),VLOOKUP(A15,week1:Data !E1,5,FALSE)) C16: =IF(ISNA(VLOOKUP(A16,week1:Data!E2,5,FALSE)),VLOOK UP(A16,el:Data!F16,5,FALSE),VLOOKUP(A16,week1:Data !E2,5,FALSE)) C17: =IF(ISNA(VLOOKUP(A17,mr:Data!G17,5,FALSE)),VLOOKUP (A17,sz:Data!H12,5,FALSE),VLOOKUP(A17,mr:Data!G17, 5,FALSE)) C18: =IF(ISNA(VLOOKUP(A18,mr:Data!G18,5,FALSE)),VLOOKUP (A18,sz:Data!H13,5,FALSE),VLOOKUP(A18,mr:Data!G18, 5,FALSE)) The formulas in C15 and C16 look for data in the "ad" and "el" options, and C17 and C18 look for "mr" and "sz". They are not directly related to B1, so I don't know if this will be a valid solution for you. Miguel. "dpatte601" wrote: Here is the worksheet , Maybe you can understand what i want if you see it at work http://www.savefile.com/files/8153879 Hi, I don't know if I have understood correctly, but I have faced similar situations and this an approach that worked for me: 1. Name the ranges where the list values are, including all the rows and columns that will be searched using VLOOKUP (can be the same as you have now, TB, EL, SZ, MR) 2. In the cell $B$1, or wherever you are selecting the code, make the dropdown with the names that you have created in point 1 3. Use the INDIRECT in the VLOOKUP formula, something like this: =VLOOKUP(A5,INDIRECT($B$1),5,FALSE) The formula will search the named range that is represented in that cell. Hope this helps, Miguel. |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
VLOOKUP and Multi Lists
?B?TWlndWVsIFphcGljbw==?=
wrote in : =IF(ISNA(VLOOKUP(A16,week1:Data!E2,5,FALSE)),VLOOK UP(A16,el:Data!F16,5, FALSE),VLOOKUP(A16,week1:Data!E2,5,FALSE)) this may work (with some work) but still if I change A15 so it now lists MR then the #N/A comes up I need it to change as I select different lists in B1. You see this is the mainpage of the data base that has other pages (not listed) that hold the CD names and band info. such as Name ,CD number, Type of Music. there are 4 pages 1 is AD (from A to D) then there is E to L , M to R , and S to Z. What I need is a formula that can always hold the last B1 selection in the cells even if B1 changes. this formula =IF(ISNA(VLOOKUP(A16,week1:Data!E2,5,FALSE)),VLOOK UP(A16,el:Data! F16,5,FALSE),VLOOKUP(A16,week1:Data!E2,5,FALSE)) needs to incorporate MR and SZ as well as AD and EL Hope this help you understand what Im looking for. thanks |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
VLOOKUP and Multi Lists
Thanks for the explanation of the codes, with that, maybe this formula will
do what you need: =IF(LEFT(A15,1)<"E",VLOOKUP(A15,week1:Data!E1,5,FA LSE),IF(LEFT(A15,1)<"M",VLOOKUP(A15,el:Data!F1,5,F ALSE),IF(LEFT(A15,1)<"S",VLOOKUP(A15,mr:Data!G1,5, FALSE),VLOOKUP(A15,sz:Data!H1,5,FALSE)))) It checks the first letter of A15 to pick the correct list to search, and it covers the four possibilities. Is this it? Miguel. "dpatte601" wrote: ?B?TWlndWVsIFphcGljbw==?= wrote in : =IF(ISNA(VLOOKUP(A16,week1:Data!E2,5,FALSE)),VLOOK UP(A16,el:Data!F16,5, FALSE),VLOOKUP(A16,week1:Data!E2,5,FALSE)) this may work (with some work) but still if I change A15 so it now lists MR then the #N/A comes up I need it to change as I select different lists in B1. You see this is the mainpage of the data base that has other pages (not listed) that hold the CD names and band info. such as Name ,CD number, Type of Music. there are 4 pages 1 is AD (from A to D) then there is E to L , M to R , and S to Z. What I need is a formula that can always hold the last B1 selection in the cells even if B1 changes. this formula =IF(ISNA(VLOOKUP(A16,week1:Data!E2,5,FALSE)),VLOOK UP(A16,el:Data! F16,5,FALSE),VLOOKUP(A16,week1:Data!E2,5,FALSE)) needs to incorporate MR and SZ as well as AD and EL Hope this help you understand what Im looking for. thanks |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
VLOOKUP and Multi Lists
?B?TWlndWVsIFphcGljbw==?=
wrote in : =IF(LEFT(A15,1)<"E",VLOOKUP(A15,week1:Data!E1,5,FA LSE),IF(LEFT(A15,1)<" M",VLOOKUP(A15,el:Data!F1,5,FALSE),IF(LEFT(A15,1)< "S",VLOOKUP(A15,mr:Da ta!G1,5,FALSE),VLOOKUP(A15,sz:Data!H1,5,FALSE)))) this was perfect - your great it worked the way I wanted it to. Many thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP and Multi Lists | Excel Worksheet Functions | |||
multi sheet lookup with multiple results | Excel Discussion (Misc queries) | |||
Vlookup with multi values | Excel Worksheet Functions | |||
add values using vlookup over multi sheets | Excel Discussion (Misc queries) | |||
VLOOKUP/ SUMIF not sufficient | Excel Discussion (Misc queries) |