Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data validation list & vlookup
Hi, I think what I am trying to do is have the data valadation lists
be used to pull data from one spreadsheet over from another spreadsheets table. Easy enough to use a vlookup for one criteria but it gets tricky when I try to get data based on two criterias. Example would be first worksheet has pull down list with cars in cell A1, Ford,Chevy,Dodge,etc ..the second pull down list has the colors in cell C1: red,blue,white,etc, I want to pull data from a second spreadsheet that has the table with the data table Color Ford Chevy Dodge White 1 2 1 Red 1 4 2 Blue 3 0 1 The problem is I need two criteria. I need the data based on the criteria selected by the two drop down list..Example If I need to know who many blue Fords I have then I select the pull down for Ford and Blue and my answer is 3. Please don't suggest a filter as I am creating a form and don't want to filter info but rather do some kind of vlookup or something based on these two criteria thank, Bill |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data validation list & vlookup
Try this:
=VLOOKUP(C1,Sheet2!A1:D4,MATCH(A1,Sheet2!A1:D1,0), 0) -- Biff Microsoft Excel MVP "Thomp" wrote in message ... Hi, I think what I am trying to do is have the data valadation lists be used to pull data from one spreadsheet over from another spreadsheets table. Easy enough to use a vlookup for one criteria but it gets tricky when I try to get data based on two criterias. Example would be first worksheet has pull down list with cars in cell A1, Ford,Chevy,Dodge,etc ..the second pull down list has the colors in cell C1: red,blue,white,etc, I want to pull data from a second spreadsheet that has the table with the data table Color Ford Chevy Dodge White 1 2 1 Red 1 4 2 Blue 3 0 1 The problem is I need two criteria. I need the data based on the criteria selected by the two drop down list..Example If I need to know who many blue Fords I have then I select the pull down for Ford and Blue and my answer is 3. Please don't suggest a filter as I am creating a form and don't want to filter info but rather do some kind of vlookup or something based on these two criteria thank, Bill |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data validation list & vlookup
I would suggest using dependent DV lists rather than two distinct DV
dropdowns. See Debra Dalgleish's site for instructions. http://www.contextures.on.ca/xlDataVal02.html Pick Ford from the list and you are presented with a dropdown of colors available from Ford. Select Blue and have a VLOOKUP formula pull 3 from Blue Fords Gord Dibben MS Excel MVP On Wed, 1 Apr 2009 13:45:42 -0700 (PDT), Thomp wrote: Hi, I think what I am trying to do is have the data valadation lists be used to pull data from one spreadsheet over from another spreadsheets table. Easy enough to use a vlookup for one criteria but it gets tricky when I try to get data based on two criterias. Example would be first worksheet has pull down list with cars in cell A1, Ford,Chevy,Dodge,etc ..the second pull down list has the colors in cell C1: red,blue,white,etc, I want to pull data from a second spreadsheet that has the table with the data table Color Ford Chevy Dodge White 1 2 1 Red 1 4 2 Blue 3 0 1 The problem is I need two criteria. I need the data based on the criteria selected by the two drop down list..Example If I need to know who many blue Fords I have then I select the pull down for Ford and Blue and my answer is 3. Please don't suggest a filter as I am creating a form and don't want to filter info but rather do some kind of vlookup or something based on these two criteria thank, Bill |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data validation list & vlookup
On Apr 1, 4:09*pm, "T. Valko" wrote:
Try this: =VLOOKUP(C1,Sheet2!A1:D4,MATCH(A1,Sheet2!A1:D1,0), 0) -- Biff Microsoft Excel MVP "Thomp" wrote in message ... Hi, I think what I am trying to do is have the data valadation lists be used to pull data from one spreadsheet over from another spreadsheets table. *Easy enough to use a vlookup for one criteria but it gets tricky when I try to get data based on two criterias. Example would be first worksheet has pull down list with cars in cell A1, Ford,Chevy,Dodge,etc ..the second pull down list has the colors in cell C1: red,blue,white,etc, I want to pull data from a second spreadsheet that has the table with the data table Color Ford Chevy Dodge White 1 2 1 Red 1 4 2 Blue 3 0 1 The problem is I need two criteria. I need the data based on the criteria selected by the two drop down list..Example If I need to know who many blue Fords I have then I select the pull down for Ford and Blue and my answer is 3. Please don't suggest a filter as I am creating a form and don't want to filter info but rather do some kind of vlookup or something based on these two criteria thank, Bill- Hide quoted text - - Show quoted text - For some reason it doesn't seem to work. Here is the actual formula I am using and the spreadsheet data =VLOOKUP(A3,Sheet1!A1:J9,MATCH(B3,Sheet1!A1:J9,0), 0) KPI Southwest Southeast Northeast Northwest Central San Francisco New England Northern Border Southern Border KPI_1a 11 28 18 49 21 3 22 4 2 KPI_1b 12 20 30 25 54 32 22 1 5 KPI_1c 10 5 5 5 5 5 12 11 10 KPI_1d 34 22 55 55 56 42 41 11 12 KPI_2a 12 29 30 51 22 4 23 5 4 KPI_2b 2 15 24 21 54 61 20 2 4 KPI_2c 7 4 3 2 7 5 4 10 7 KPI_2d 30 20 50 45 42 41 40 10 10 Sorry, Paste doesn't work so well on this site but I hope you get the picture. The two data valadation list are in cells A3 and B1 on Sheet 2 (3) thanks, Bill |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data validation list & vlookup
=VLOOKUP(A3,Sheet1!A1:J9,MATCH(B3,Sheet1!A1:J9,0) ,0)
The two data valadation list are in cells A3 and B1 VLOOKUP(A3... is the vertical lookup MATCH(B3... is the horizontal lookup The horizontal lookup must be to a single row. Try it like this: =VLOOKUP(A3,Sheet1!A1:J9,MATCH(B1,Sheet1!A1:J1,0), 0) Note how I changed the horizontal lookup range to a single row. -- Biff Microsoft Excel MVP "Thomp" wrote in message ... On Apr 1, 4:09 pm, "T. Valko" wrote: Try this: =VLOOKUP(C1,Sheet2!A1:D4,MATCH(A1,Sheet2!A1:D1,0), 0) -- Biff Microsoft Excel MVP "Thomp" wrote in message ... Hi, I think what I am trying to do is have the data valadation lists be used to pull data from one spreadsheet over from another spreadsheets table. Easy enough to use a vlookup for one criteria but it gets tricky when I try to get data based on two criterias. Example would be first worksheet has pull down list with cars in cell A1, Ford,Chevy,Dodge,etc ..the second pull down list has the colors in cell C1: red,blue,white,etc, I want to pull data from a second spreadsheet that has the table with the data table Color Ford Chevy Dodge White 1 2 1 Red 1 4 2 Blue 3 0 1 The problem is I need two criteria. I need the data based on the criteria selected by the two drop down list..Example If I need to know who many blue Fords I have then I select the pull down for Ford and Blue and my answer is 3. Please don't suggest a filter as I am creating a form and don't want to filter info but rather do some kind of vlookup or something based on these two criteria thank, Bill- Hide quoted text - - Show quoted text - For some reason it doesn't seem to work. Here is the actual formula I am using and the spreadsheet data =VLOOKUP(A3,Sheet1!A1:J9,MATCH(B3,Sheet1!A1:J9,0), 0) KPI Southwest Southeast Northeast Northwest Central San Francisco New England Northern Border Southern Border KPI_1a 11 28 18 49 21 3 22 4 2 KPI_1b 12 20 30 25 54 32 22 1 5 KPI_1c 10 5 5 5 5 5 12 11 10 KPI_1d 34 22 55 55 56 42 41 11 12 KPI_2a 12 29 30 51 22 4 23 5 4 KPI_2b 2 15 24 21 54 61 20 2 4 KPI_2c 7 4 3 2 7 5 4 10 7 KPI_2d 30 20 50 45 42 41 40 10 10 Sorry, Paste doesn't work so well on this site but I hope you get the picture. The two data valadation list are in cells A3 and B1 on Sheet 2 (3) thanks, Bill |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using vlookup on a validation list | Excel Worksheet Functions | |||
Help w/Validation List & VLOOKUP | Excel Worksheet Functions | |||
If, vlookup, data validation & dependent list | Excel Worksheet Functions | |||
data validation invalid in dynamic validation list | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Worksheet Functions |