| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup and validating data list
I use Excel 2007, but will need to share this workbook with 2003 users.
On worksheet 1, I want to pick from a list of possible options for info that goes into column for Subs from the row that matches the Mfg on worksheet 2. Not all Mfg may have a Sub option, therefore I may want to repeat the Mfg name. Both worksheets will have adds and deletes over the course of the year. Worksheet 1 Parts Mfg Sub 123 Stars ?? 234 Color ?? 345 Cars ?? 888 Color ?? 777 Color ?? 557 Cars ?? 735 States ?? Worksheet 2 Mfg Sub Stars Venus Mars Saturn Jupiter Color Blue Black Red Green Yellow Cars Ford GM Toyato States I tried the vlookup statement, but it only returns the value from the column that I define in the formula in worksheet 1 C2 =VLOOKUP(B2,'Worksheet 2'!A$2:H1066,2) Answer would be Venus, but I would like for it to be Jupiter. Because worksheet 2 is so large, it would be hard to create a name range for each row. Any suggestions? Thanks so much |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup and validating data list
I'm not sure I understand it all, but your vlookup will return Jupiter if you
change the vlookup from ...,2) to ...,5) good luck... Jim "Donna" wrote: I use Excel 2007, but will need to share this workbook with 2003 users. On worksheet 1, I want to pick from a list of possible options for info that goes into column for Subs from the row that matches the Mfg on worksheet 2. Not all Mfg may have a Sub option, therefore I may want to repeat the Mfg name. Both worksheets will have adds and deletes over the course of the year. Worksheet 1 Parts Mfg Sub 123 Stars ?? 234 Color ?? 345 Cars ?? 888 Color ?? 777 Color ?? 557 Cars ?? 735 States ?? Worksheet 2 Mfg Sub Stars Venus Mars Saturn Jupiter Color Blue Black Red Green Yellow Cars Ford GM Toyato States I tried the vlookup statement, but it only returns the value from the column that I define in the formula in worksheet 1 C2 =VLOOKUP(B2,'Worksheet 2'!A$2:H1066,2) Answer would be Venus, but I would like for it to be Jupiter. Because worksheet 2 is so large, it would be hard to create a name range for each row. Any suggestions? Thanks so much |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup and validating data list
True, but I don't always know the column B-H that contains the answer,
therefore, was hoping to create a list for that row to choose from after it matches col A. "Jim" wrote: I'm not sure I understand it all, but your vlookup will return Jupiter if you change the vlookup from ...,2) to ...,5) good luck... Jim "Donna" wrote: I use Excel 2007, but will need to share this workbook with 2003 users. On worksheet 1, I want to pick from a list of possible options for info that goes into column for Subs from the row that matches the Mfg on worksheet 2. Not all Mfg may have a Sub option, therefore I may want to repeat the Mfg name. Both worksheets will have adds and deletes over the course of the year. Worksheet 1 Parts Mfg Sub 123 Stars ?? 234 Color ?? 345 Cars ?? 888 Color ?? 777 Color ?? 557 Cars ?? 735 States ?? Worksheet 2 Mfg Sub Stars Venus Mars Saturn Jupiter Color Blue Black Red Green Yellow Cars Ford GM Toyato States I tried the vlookup statement, but it only returns the value from the column that I define in the formula in worksheet 1 C2 =VLOOKUP(B2,'Worksheet 2'!A$2:H1066,2) Answer would be Venus, but I would like for it to be Jupiter. Because worksheet 2 is so large, it would be hard to create a name range for each row. Any suggestions? Thanks so much |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup and validating data list
Let's see if I understand you...
If B2 = Stars then you want a drop down list in C2 with the selections: Stars,Venus,Mars,Saturn,Jupiter If B2 = Cars then you want a drop down list in C2 with the selections: Cars,Ford,GM,Toyato If B2 = States then you want a drop down list in C2 with the selections: States If that's the case... With this data on Sheet2 in the range A2:F5 - Stars,Venus,Mars,Saturn,Jupiter Color,Blue,Black,Red,Green,Yellow Cars,Ford,GM,Toyato States Create this named formula: InsertNameDefine Name: List Refers to: =OFFSET(Sheet2!$A$2,MATCH($B$2,Sheet2!$A$2:$A$5,0)-1,,,COUNTA(OFFSET(Sheet2!$A$2,MATCH($B$2,Sheet2!$A $2:$A$5,0)-1,,,100))) Then on Sheet1 select cell C2 DataValidation Allow: List Source: =List -- Biff Microsoft Excel MVP "Donna" wrote in message ... I use Excel 2007, but will need to share this workbook with 2003 users. On worksheet 1, I want to pick from a list of possible options for info that goes into column for Subs from the row that matches the Mfg on worksheet 2. Not all Mfg may have a Sub option, therefore I may want to repeat the Mfg name. Both worksheets will have adds and deletes over the course of the year. Worksheet 1 Parts Mfg Sub 123 Stars ?? 234 Color ?? 345 Cars ?? 888 Color ?? 777 Color ?? 557 Cars ?? 735 States ?? Worksheet 2 Mfg Sub Stars Venus Mars Saturn Jupiter Color Blue Black Red Green Yellow Cars Ford GM Toyato States I tried the vlookup statement, but it only returns the value from the column that I define in the formula in worksheet 1 C2 =VLOOKUP(B2,'Worksheet 2'!A$2:H1066,2) Answer would be Venus, but I would like for it to be Jupiter. Because worksheet 2 is so large, it would be hard to create a name range for each row. Any suggestions? Thanks so much |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup and validating data list
Thanks so much for the reply,. Your assessment is correct, but I've not be
able to get it to work. After last step doing the DataValidation, I get an error: "The Source currently evaluates to an error. Do you want to continue? Yes No" When I select Yes, it has the drop down button, but there is no list. You made reference to the range of A2:F5 but in the formula it was A2:A5. Is that correct? "T. Valko" wrote: Let's see if I understand you... If B2 = Stars then you want a drop down list in C2 with the selections: Stars,Venus,Mars,Saturn,Jupiter If B2 = Cars then you want a drop down list in C2 with the selections: Cars,Ford,GM,Toyato If B2 = States then you want a drop down list in C2 with the selections: States If that's the case... With this data on Sheet2 in the range A2:F5 - Stars,Venus,Mars,Saturn,Jupiter Color,Blue,Black,Red,Green,Yellow Cars,Ford,GM,Toyato States Create this named formula: InsertNameDefine Name: List Refers to: =OFFSET(Sheet2!$A$2,MATCH($B$2,Sheet2!$A$2:$A$5,0)-1,,,COUNTA(OFFSET(Sheet2!$A$2,MATCH($B$2,Sheet2!$A $2:$A$5,0)-1,,,100))) Then on Sheet1 select cell C2 DataValidation Allow: List Source: =List -- Biff Microsoft Excel MVP "Donna" wrote in message ... I use Excel 2007, but will need to share this workbook with 2003 users. On worksheet 1, I want to pick from a list of possible options for info that goes into column for Subs from the row that matches the Mfg on worksheet 2. Not all Mfg may have a Sub option, therefore I may want to repeat the Mfg name. Both worksheets will have adds and deletes over the course of the year. Worksheet 1 Parts Mfg Sub 123 Stars ?? 234 Color ?? 345 Cars ?? 888 Color ?? 777 Color ?? 557 Cars ?? 735 States ?? Worksheet 2 Mfg Sub Stars Venus Mars Saturn Jupiter Color Blue Black Red Green Yellow Cars Ford GM Toyato States I tried the vlookup statement, but it only returns the value from the column that I define in the formula in worksheet 1 C2 =VLOOKUP(B2,'Worksheet 2'!A$2:H1066,2) Answer would be Venus, but I would like for it to be Jupiter. Because worksheet 2 is so large, it would be hard to create a name range for each row. Any suggestions? Thanks so much |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup and validating data list
Here's a small sample file that demonstrates this:
xdropdown.xls 14kb http://cjoint.com/?cCtoxeKAzM -- Biff Microsoft Excel MVP "Donna" wrote in message ... Thanks so much for the reply,. Your assessment is correct, but I've not be able to get it to work. After last step doing the DataValidation, I get an error: "The Source currently evaluates to an error. Do you want to continue? Yes No" When I select Yes, it has the drop down button, but there is no list. You made reference to the range of A2:F5 but in the formula it was A2:A5. Is that correct? "T. Valko" wrote: Let's see if I understand you... If B2 = Stars then you want a drop down list in C2 with the selections: Stars,Venus,Mars,Saturn,Jupiter If B2 = Cars then you want a drop down list in C2 with the selections: Cars,Ford,GM,Toyato If B2 = States then you want a drop down list in C2 with the selections: States If that's the case... With this data on Sheet2 in the range A2:F5 - Stars,Venus,Mars,Saturn,Jupiter Color,Blue,Black,Red,Green,Yellow Cars,Ford,GM,Toyato States Create this named formula: InsertNameDefine Name: List Refers to: =OFFSET(Sheet2!$A$2,MATCH($B$2,Sheet2!$A$2:$A$5,0)-1,,,COUNTA(OFFSET(Sheet2!$A$2,MATCH($B$2,Sheet2!$A $2:$A$5,0)-1,,,100))) Then on Sheet1 select cell C2 DataValidation Allow: List Source: =List -- Biff Microsoft Excel MVP "Donna" wrote in message ... I use Excel 2007, but will need to share this workbook with 2003 users. On worksheet 1, I want to pick from a list of possible options for info that goes into column for Subs from the row that matches the Mfg on worksheet 2. Not all Mfg may have a Sub option, therefore I may want to repeat the Mfg name. Both worksheets will have adds and deletes over the course of the year. Worksheet 1 Parts Mfg Sub 123 Stars ?? 234 Color ?? 345 Cars ?? 888 Color ?? 777 Color ?? 557 Cars ?? 735 States ?? Worksheet 2 Mfg Sub Stars Venus Mars Saturn Jupiter Color Blue Black Red Green Yellow Cars Ford GM Toyato States I tried the vlookup statement, but it only returns the value from the column that I define in the formula in worksheet 1 C2 =VLOOKUP(B2,'Worksheet 2'!A$2:H1066,2) Answer would be Venus, but I would like for it to be Jupiter. Because worksheet 2 is so large, it would be hard to create a name range for each row. Any suggestions? Thanks so much |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup and validating data list
I loved the file, very cool.
Can this also be done vertically rather than horizontally? Tim. "T. Valko" wrote: Here's a small sample file that demonstrates this: xdropdown.xls 14kb http://cjoint.com/?cCtoxeKAzM -- Biff Microsoft Excel MVP "Donna" wrote in message ... Thanks so much for the reply,. Your assessment is correct, but I've not be able to get it to work. After last step doing the DataValidation, I get an error: "The Source currently evaluates to an error. Do you want to continue? Yes No" When I select Yes, it has the drop down button, but there is no list. You made reference to the range of A2:F5 but in the formula it was A2:A5. Is that correct? "T. Valko" wrote: Let's see if I understand you... If B2 = Stars then you want a drop down list in C2 with the selections: Stars,Venus,Mars,Saturn,Jupiter If B2 = Cars then you want a drop down list in C2 with the selections: Cars,Ford,GM,Toyato If B2 = States then you want a drop down list in C2 with the selections: States If that's the case... With this data on Sheet2 in the range A2:F5 - Stars,Venus,Mars,Saturn,Jupiter Color,Blue,Black,Red,Green,Yellow Cars,Ford,GM,Toyato States Create this named formula: InsertNameDefine Name: List Refers to: =OFFSET(Sheet2!$A$2,MATCH($B$2,Sheet2!$A$2:$A$5,0)-1,,,COUNTA(OFFSET(Sheet2!$A$2,MATCH($B$2,Sheet2!$A $2:$A$5,0)-1,,,100))) Then on Sheet1 select cell C2 DataValidation Allow: List Source: =List -- Biff Microsoft Excel MVP "Donna" wrote in message ... I use Excel 2007, but will need to share this workbook with 2003 users. On worksheet 1, I want to pick from a list of possible options for info that goes into column for Subs from the row that matches the Mfg on worksheet 2. Not all Mfg may have a Sub option, therefore I may want to repeat the Mfg name. Both worksheets will have adds and deletes over the course of the year. Worksheet 1 Parts Mfg Sub 123 Stars ?? 234 Color ?? 345 Cars ?? 888 Color ?? 777 Color ?? 557 Cars ?? 735 States ?? Worksheet 2 Mfg Sub Stars Venus Mars Saturn Jupiter Color Blue Black Red Green Yellow Cars Ford GM Toyato States I tried the vlookup statement, but it only returns the value from the column that I define in the formula in worksheet 1 C2 =VLOOKUP(B2,'Worksheet 2'!A$2:H1066,2) Answer would be Venus, but I would like for it to be Jupiter. Because worksheet 2 is so large, it would be hard to create a name range for each row. Any suggestions? Thanks so much |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup and validating data list
Can this also be done vertically rather than horizontally?
Sure. Assume this data on Sheet2 A2: D7 - Stars,Color,Cars,States Venus,Blue,Ford Mars,Black,GM Saturn,Red,Toyato Jupiter,Green Yellow InsertNameDefine Name: List Refers to: =OFFSET(Sheet2!$A$2,,MATCH(Sheet1!$B$2,Sheet2!$2:$ 2,0)-1,COUNTA(OFFSET(Sheet2!$A$2,,MATCH(Sheet1!$B$2,She et2!$2:$2,0)-1,100))) DataValidation Allow: List Source: =List -- Biff Microsoft Excel MVP "Tim Brown" wrote in message ... I loved the file, very cool. Can this also be done vertically rather than horizontally? Tim. "T. Valko" wrote: Here's a small sample file that demonstrates this: xdropdown.xls 14kb http://cjoint.com/?cCtoxeKAzM -- Biff Microsoft Excel MVP "Donna" wrote in message ... Thanks so much for the reply,. Your assessment is correct, but I've not be able to get it to work. After last step doing the DataValidation, I get an error: "The Source currently evaluates to an error. Do you want to continue? Yes No" When I select Yes, it has the drop down button, but there is no list. You made reference to the range of A2:F5 but in the formula it was A2:A5. Is that correct? "T. Valko" wrote: Let's see if I understand you... If B2 = Stars then you want a drop down list in C2 with the selections: Stars,Venus,Mars,Saturn,Jupiter If B2 = Cars then you want a drop down list in C2 with the selections: Cars,Ford,GM,Toyato If B2 = States then you want a drop down list in C2 with the selections: States If that's the case... With this data on Sheet2 in the range A2:F5 - Stars,Venus,Mars,Saturn,Jupiter Color,Blue,Black,Red,Green,Yellow Cars,Ford,GM,Toyato States Create this named formula: InsertNameDefine Name: List Refers to: =OFFSET(Sheet2!$A$2,MATCH($B$2,Sheet2!$A$2:$A$5,0)-1,,,COUNTA(OFFSET(Sheet2!$A$2,MATCH($B$2,Sheet2!$A $2:$A$5,0)-1,,,100))) Then on Sheet1 select cell C2 DataValidation Allow: List Source: =List -- Biff Microsoft Excel MVP "Donna" wrote in message ... I use Excel 2007, but will need to share this workbook with 2003 users. On worksheet 1, I want to pick from a list of possible options for info that goes into column for Subs from the row that matches the Mfg on worksheet 2. Not all Mfg may have a Sub option, therefore I may want to repeat the Mfg name. Both worksheets will have adds and deletes over the course of the year. Worksheet 1 Parts Mfg Sub 123 Stars ?? 234 Color ?? 345 Cars ?? 888 Color ?? 777 Color ?? 557 Cars ?? 735 States ?? Worksheet 2 Mfg Sub Stars Venus Mars Saturn Jupiter Color Blue Black Red Green Yellow Cars Ford GM Toyato States I tried the vlookup statement, but it only returns the value from the column that I define in the formula in worksheet 1 C2 =VLOOKUP(B2,'Worksheet 2'!A$2:H1066,2) Answer would be Venus, but I would like for it to be Jupiter. Because worksheet 2 is so large, it would be hard to create a name range for each row. Any suggestions? Thanks so much |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup and validating data list
I was able to get the list to work, but the validation only works with the
first column. Any sugestions? I tried it in a small scale and it worked fine. could there be something different with the workbooks? =OFFSET(Tables!$B$1,,MATCH('Tracking Form'!$F$1,Tables!$1:$1,0)-1,COUNTA(OFFSET(Tables!$B$1,,MATCH('Tracking Form'!$F$1,Tables!$1:$1,0)-1,100))) Tim "T. Valko" wrote: Can this also be done vertically rather than horizontally? Sure. Assume this data on Sheet2 A2: D7 - Stars,Color,Cars,States Venus,Blue,Ford Mars,Black,GM Saturn,Red,Toyato Jupiter,Green Yellow InsertNameDefine Name: List Refers to: =OFFSET(Sheet2!$A$2,,MATCH(Sheet1!$B$2,Sheet2!$2:$ 2,0)-1,COUNTA(OFFSET(Sheet2!$A$2,,MATCH(Sheet1!$B$2,She et2!$2:$2,0)-1,100))) DataValidation Allow: List Source: =List -- Biff Microsoft Excel MVP "Tim Brown" wrote in message ... I loved the file, very cool. Can this also be done vertically rather than horizontally? Tim. "T. Valko" wrote: Here's a small sample file that demonstrates this: xdropdown.xls 14kb http://cjoint.com/?cCtoxeKAzM -- Biff Microsoft Excel MVP "Donna" wrote in message ... Thanks so much for the reply,. Your assessment is correct, but I've not be able to get it to work. After last step doing the DataValidation, I get an error: "The Source currently evaluates to an error. Do you want to continue? Yes No" When I select Yes, it has the drop down button, but there is no list. You made reference to the range of A2:F5 but in the formula it was A2:A5. Is that correct? "T. Valko" wrote: Let's see if I understand you... If B2 = Stars then you want a drop down list in C2 with the selections: Stars,Venus,Mars,Saturn,Jupiter If B2 = Cars then you want a drop down list in C2 with the selections: Cars,Ford,GM,Toyato If B2 = States then you want a drop down list in C2 with the selections: States If that's the case... With this data on Sheet2 in the range A2:F5 - Stars,Venus,Mars,Saturn,Jupiter Color,Blue,Black,Red,Green,Yellow Cars,Ford,GM,Toyato States Create this named formula: InsertNameDefine Name: List Refers to: =OFFSET(Sheet2!$A$2,MATCH($B$2,Sheet2!$A$2:$A$5,0)-1,,,COUNTA(OFFSET(Sheet2!$A$2,MATCH($B$2,Sheet2!$A $2:$A$5,0)-1,,,100))) Then on Sheet1 select cell C2 DataValidation Allow: List Source: =List -- Biff Microsoft Excel MVP "Donna" wrote in message ... I use Excel 2007, but will need to share this workbook with 2003 users. On worksheet 1, I want to pick from a list of possible options for info that goes into column for Subs from the row that matches the Mfg on worksheet 2. Not all Mfg may have a Sub option, therefore I may want to repeat the Mfg name. Both worksheets will have adds and deletes over the course of the year. Worksheet 1 Parts Mfg Sub 123 Stars ?? 234 Color ?? 345 Cars ?? 888 Color ?? 777 Color ?? 557 Cars ?? 735 States ?? Worksheet 2 Mfg Sub Stars Venus Mars Saturn Jupiter Color Blue Black Red Green Yellow Cars Ford GM Toyato States I tried the vlookup statement, but it only returns the value from the column that I define in the formula in worksheet 1 C2 =VLOOKUP(B2,'Worksheet 2'!A$2:H1066,2) Answer would be Venus, but I would like for it to be Jupiter. Because worksheet 2 is so large, it would be hard to create a name range for each row. Any suggestions? Thanks so much |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup and validating data list
Not sure what you mean by:
the validation only works with the first column -- Biff Microsoft Excel MVP "Tim Brown" wrote in message ... I was able to get the list to work, but the validation only works with the first column. Any sugestions? I tried it in a small scale and it worked fine. could there be something different with the workbooks? =OFFSET(Tables!$B$1,,MATCH('Tracking Form'!$F$1,Tables!$1:$1,0)-1,COUNTA(OFFSET(Tables!$B$1,,MATCH('Tracking Form'!$F$1,Tables!$1:$1,0)-1,100))) Tim "T. Valko" wrote: Can this also be done vertically rather than horizontally? Sure. Assume this data on Sheet2 A2: D7 - Stars,Color,Cars,States Venus,Blue,Ford Mars,Black,GM Saturn,Red,Toyato Jupiter,Green Yellow InsertNameDefine Name: List Refers to: =OFFSET(Sheet2!$A$2,,MATCH(Sheet1!$B$2,Sheet2!$2:$ 2,0)-1,COUNTA(OFFSET(Sheet2!$A$2,,MATCH(Sheet1!$B$2,She et2!$2:$2,0)-1,100))) DataValidation Allow: List Source: =List -- Biff Microsoft Excel MVP "Tim Brown" wrote in message ... I loved the file, very cool. Can this also be done vertically rather than horizontally? Tim. "T. Valko" wrote: Here's a small sample file that demonstrates this: xdropdown.xls 14kb http://cjoint.com/?cCtoxeKAzM -- Biff Microsoft Excel MVP "Donna" wrote in message ... Thanks so much for the reply,. Your assessment is correct, but I've not be able to get it to work. After last step doing the DataValidation, I get an error: "The Source currently evaluates to an error. Do you want to continue? Yes No" When I select Yes, it has the drop down button, but there is no list. You made reference to the range of A2:F5 but in the formula it was A2:A5. Is that correct? "T. Valko" wrote: Let's see if I understand you... If B2 = Stars then you want a drop down list in C2 with the selections: Stars,Venus,Mars,Saturn,Jupiter If B2 = Cars then you want a drop down list in C2 with the selections: Cars,Ford,GM,Toyato If B2 = States then you want a drop down list in C2 with the selections: States If that's the case... With this data on Sheet2 in the range A2:F5 - Stars,Venus,Mars,Saturn,Jupiter Color,Blue,Black,Red,Green,Yellow Cars,Ford,GM,Toyato States Create this named formula: InsertNameDefine Name: List Refers to: =OFFSET(Sheet2!$A$2,MATCH($B$2,Sheet2!$A$2:$A$5,0)-1,,,COUNTA(OFFSET(Sheet2!$A$2,MATCH($B$2,Sheet2!$A $2:$A$5,0)-1,,,100))) Then on Sheet1 select cell C2 DataValidation Allow: List Source: =List -- Biff Microsoft Excel MVP "Donna" wrote in message ... I use Excel 2007, but will need to share this workbook with 2003 users. On worksheet 1, I want to pick from a list of possible options for info that goes into column for Subs from the row that matches the Mfg on worksheet 2. Not all Mfg may have a Sub option, therefore I may want to repeat the Mfg name. Both worksheets will have adds and deletes over the course of the year. Worksheet 1 Parts Mfg Sub 123 Stars ?? 234 Color ?? 345 Cars ?? 888 Color ?? 777 Color ?? 557 Cars ?? 735 States ?? Worksheet 2 Mfg Sub Stars Venus Mars Saturn Jupiter Color Blue Black Red Green Yellow Cars Ford GM Toyato States I tried the vlookup statement, but it only returns the value from the column that I define in the formula in worksheet 1 C2 =VLOOKUP(B2,'Worksheet 2'!A$2:H1066,2) Answer would be Venus, but I would like for it to be Jupiter. Because worksheet 2 is so large, it would be hard to create a name range for each row. Any suggestions? Thanks so much |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup and validating data list
I was able to enter data that was not in the second column when that column
was picked. - I did some reading and found that I had blank spaces in the second column. I unchecked the blank cells box and it works fine. Thanks for your help Tim "T. Valko" wrote: Not sure what you mean by: the validation only works with the first column -- Biff Microsoft Excel MVP "Tim Brown" wrote in message ... I was able to get the list to work, but the validation only works with the first column. Any sugestions? I tried it in a small scale and it worked fine. could there be something different with the workbooks? =OFFSET(Tables!$B$1,,MATCH('Tracking Form'!$F$1,Tables!$1:$1,0)-1,COUNTA(OFFSET(Tables!$B$1,,MATCH('Tracking Form'!$F$1,Tables!$1:$1,0)-1,100))) Tim "T. Valko" wrote: Can this also be done vertically rather than horizontally? Sure. Assume this data on Sheet2 A2: D7 - Stars,Color,Cars,States Venus,Blue,Ford Mars,Black,GM Saturn,Red,Toyato Jupiter,Green Yellow InsertNameDefine Name: List Refers to: =OFFSET(Sheet2!$A$2,,MATCH(Sheet1!$B$2,Sheet2!$2:$ 2,0)-1,COUNTA(OFFSET(Sheet2!$A$2,,MATCH(Sheet1!$B$2,She et2!$2:$2,0)-1,100))) DataValidation Allow: List Source: =List -- Biff Microsoft Excel MVP "Tim Brown" wrote in message ... I loved the file, very cool. Can this also be done vertically rather than horizontally? Tim. "T. Valko" wrote: Here's a small sample file that demonstrates this: xdropdown.xls 14kb http://cjoint.com/?cCtoxeKAzM -- Biff Microsoft Excel MVP "Donna" wrote in message ... Thanks so much for the reply,. Your assessment is correct, but I've not be able to get it to work. After last step doing the DataValidation, I get an error: "The Source currently evaluates to an error. Do you want to continue? Yes No" When I select Yes, it has the drop down button, but there is no list. You made reference to the range of A2:F5 but in the formula it was A2:A5. Is that correct? "T. Valko" wrote: Let's see if I understand you... If B2 = Stars then you want a drop down list in C2 with the selections: Stars,Venus,Mars,Saturn,Jupiter If B2 = Cars then you want a drop down list in C2 with the selections: Cars,Ford,GM,Toyato If B2 = States then you want a drop down list in C2 with the selections: States If that's the case... With this data on Sheet2 in the range A2:F5 - Stars,Venus,Mars,Saturn,Jupiter Color,Blue,Black,Red,Green,Yellow Cars,Ford,GM,Toyato States Create this named formula: InsertNameDefine Name: List Refers to: =OFFSET(Sheet2!$A$2,MATCH($B$2,Sheet2!$A$2:$A$5,0)-1,,,COUNTA(OFFSET(Sheet2!$A$2,MATCH($B$2,Sheet2!$A $2:$A$5,0)-1,,,100))) Then on Sheet1 select cell C2 DataValidation Allow: List Source: =List -- Biff Microsoft Excel MVP "Donna" wrote in message ... I use Excel 2007, but will need to share this workbook with 2003 users. On worksheet 1, I want to pick from a list of possible options for info that goes into column for Subs from the row that matches the Mfg on worksheet 2. Not all Mfg may have a Sub option, therefore I may want to repeat the Mfg name. Both worksheets will have adds and deletes over the course of the year. Worksheet 1 Parts Mfg Sub 123 Stars ?? 234 Color ?? 345 Cars ?? 888 Color ?? 777 Color ?? 557 Cars ?? 735 States ?? Worksheet 2 Mfg Sub Stars Venus Mars Saturn Jupiter Color Blue Black Red Green Yellow Cars Ford GM Toyato States I tried the vlookup statement, but it only returns the value from the column that I define in the formula in worksheet 1 C2 =VLOOKUP(B2,'Worksheet 2'!A$2:H1066,2) Answer would be Venus, but I would like for it to be Jupiter. Because worksheet 2 is so large, it would be hard to create a name range for each row. Any suggestions? Thanks so much |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup and validating data list
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Tim Brown" wrote in message ... I was able to enter data that was not in the second column when that column was picked. - I did some reading and found that I had blank spaces in the second column. I unchecked the blank cells box and it works fine. Thanks for your help Tim "T. Valko" wrote: Not sure what you mean by: the validation only works with the first column -- Biff Microsoft Excel MVP "Tim Brown" wrote in message ... I was able to get the list to work, but the validation only works with the first column. Any sugestions? I tried it in a small scale and it worked fine. could there be something different with the workbooks? =OFFSET(Tables!$B$1,,MATCH('Tracking Form'!$F$1,Tables!$1:$1,0)-1,COUNTA(OFFSET(Tables!$B$1,,MATCH('Tracking Form'!$F$1,Tables!$1:$1,0)-1,100))) Tim "T. Valko" wrote: Can this also be done vertically rather than horizontally? Sure. Assume this data on Sheet2 A2: D7 - Stars,Color,Cars,States Venus,Blue,Ford Mars,Black,GM Saturn,Red,Toyato Jupiter,Green Yellow InsertNameDefine Name: List Refers to: =OFFSET(Sheet2!$A$2,,MATCH(Sheet1!$B$2,Sheet2!$2:$ 2,0)-1,COUNTA(OFFSET(Sheet2!$A$2,,MATCH(Sheet1!$B$2,She et2!$2:$2,0)-1,100))) DataValidation Allow: List Source: =List -- Biff Microsoft Excel MVP "Tim Brown" wrote in message ... I loved the file, very cool. Can this also be done vertically rather than horizontally? Tim. "T. Valko" wrote: Here's a small sample file that demonstrates this: xdropdown.xls 14kb http://cjoint.com/?cCtoxeKAzM -- Biff Microsoft Excel MVP "Donna" wrote in message ... Thanks so much for the reply,. Your assessment is correct, but I've not be able to get it to work. After last step doing the DataValidation, I get an error: "The Source currently evaluates to an error. Do you want to continue? Yes No" When I select Yes, it has the drop down button, but there is no list. You made reference to the range of A2:F5 but in the formula it was A2:A5. Is that correct? "T. Valko" wrote: Let's see if I understand you... If B2 = Stars then you want a drop down list in C2 with the selections: Stars,Venus,Mars,Saturn,Jupiter If B2 = Cars then you want a drop down list in C2 with the selections: Cars,Ford,GM,Toyato If B2 = States then you want a drop down list in C2 with the selections: States If that's the case... With this data on Sheet2 in the range A2:F5 - Stars,Venus,Mars,Saturn,Jupiter Color,Blue,Black,Red,Green,Yellow Cars,Ford,GM,Toyato States Create this named formula: InsertNameDefine Name: List Refers to: =OFFSET(Sheet2!$A$2,MATCH($B$2,Sheet2!$A$2:$A$5,0)-1,,,COUNTA(OFFSET(Sheet2!$A$2,MATCH($B$2,Sheet2!$A $2:$A$5,0)-1,,,100))) Then on Sheet1 select cell C2 DataValidation Allow: List Source: =List -- Biff Microsoft Excel MVP "Donna" wrote in message ... I use Excel 2007, but will need to share this workbook with 2003 users. On worksheet 1, I want to pick from a list of possible options for info that goes into column for Subs from the row that matches the Mfg on worksheet 2. Not all Mfg may have a Sub option, therefore I may want to repeat the Mfg name. Both worksheets will have adds and deletes over the course of the year. Worksheet 1 Parts Mfg Sub 123 Stars ?? 234 Color ?? 345 Cars ?? 888 Color ?? 777 Color ?? 557 Cars ?? 735 States ?? Worksheet 2 Mfg Sub Stars Venus Mars Saturn Jupiter Color Blue Black Red Green Yellow Cars Ford GM Toyato States I tried the vlookup statement, but it only returns the value from the column that I define in the formula in worksheet 1 C2 =VLOOKUP(B2,'Worksheet 2'!A$2:H1066,2) Answer would be Venus, but I would like for it to be Jupiter. Because worksheet 2 is so large, it would be hard to create a name range for each row. Any suggestions? Thanks so much |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Modifying a validating list | Excel Discussion (Misc queries) | |||
| validating a list of multiple columns | Excel Worksheet Functions | |||
| Connecting of two data validating with each other | Excel Worksheet Functions | |||
| validating data in a combobox | Excel Worksheet Functions | |||
| Validating data pasted into worksheet | Excel Discussion (Misc queries) | |||
Linear Mode
