Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a dropdown option to display records
Simplistically, my problem is this:
I would like to be able to put a combo box on sheet 1 that will allow a user to select a choice (the combo box should be populated with unique records from sheet 2, column b). Once a choice has been selected, all matching records from sheet 2 will be displayed on sheet 1. If anyone can point me in the direction of a similar example I would be grateful. -- Thanks, MarkN |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a dropdown option to display records
Use data validation rather than a combobox, as this uses a cell on the
worksheet rather than an object off the worksheet. You can then use VLOOKUP formulae to look into your table, using the value selected from the DV cell. If using a range on another worksheet to populate the DV, name that range, and use that name in DV, else you will get an error. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "MarkN" wrote in message ... Simplistically, my problem is this: I would like to be able to put a combo box on sheet 1 that will allow a user to select a choice (the combo box should be populated with unique records from sheet 2, column b). Once a choice has been selected, all matching records from sheet 2 will be displayed on sheet 1. If anyone can point me in the direction of a similar example I would be grateful. -- Thanks, MarkN |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a dropdown option to display records
Thanks for the response Bob,
I need something that will work for a generic list where unique items in column B populate the combo box. This is key, can I do this with data validation given that the list on sheet 2 is a dumped list where the values change 3 or 4 times a day? -- Thanks, MarkN "Bob Phillips" wrote: Use data validation rather than a combobox, as this uses a cell on the worksheet rather than an object off the worksheet. You can then use VLOOKUP formulae to look into your table, using the value selected from the DV cell. If using a range on another worksheet to populate the DV, name that range, and use that name in DV, else you will get an error. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "MarkN" wrote in message ... Simplistically, my problem is this: I would like to be able to put a combo box on sheet 1 that will allow a user to select a choice (the combo box should be populated with unique records from sheet 2, column b). Once a choice has been selected, all matching records from sheet 2 will be displayed on sheet 1. If anyone can point me in the direction of a similar example I would be grateful. -- Thanks, MarkN |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a dropdown option to display records
You can Mark, but it takes a little work.
Assuming your data is dumped into column A, in column B do this B1: = A1 B2: =IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""), 0)),"", INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH( 0,COUNTIF(B$1:B1,$A$1:$A$2 0&""),0))) copy B2 down as far as you will ever need. It is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Create a dynamic name for column B (InsertNameDefine...) of say MyList with a RefersTo value of =OFFSET($B$1,,,COUNTA($B:$B),1) and use MyList in the DV. I have created a little example you can look at http://cjoint.com/?edlxqUTtnT -- HTH Bob Phillips (remove nothere from email address if mailing direct) "MarkN" wrote in message ... Thanks for the response Bob, I need something that will work for a generic list where unique items in column B populate the combo box. This is key, can I do this with data validation given that the list on sheet 2 is a dumped list where the values change 3 or 4 times a day? -- Thanks, MarkN "Bob Phillips" wrote: Use data validation rather than a combobox, as this uses a cell on the worksheet rather than an object off the worksheet. You can then use VLOOKUP formulae to look into your table, using the value selected from the DV cell. If using a range on another worksheet to populate the DV, name that range, and use that name in DV, else you will get an error. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "MarkN" wrote in message ... Simplistically, my problem is this: I would like to be able to put a combo box on sheet 1 that will allow a user to select a choice (the combo box should be populated with unique records from sheet 2, column b). Once a choice has been selected, all matching records from sheet 2 will be displayed on sheet 1. If anyone can point me in the direction of a similar example I would be grateful. -- Thanks, MarkN |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a dropdown option to display records
Thanks Bob,
I will get this working and then I will try to understand it! -- Thanks, MarkN "Bob Phillips" wrote: You can Mark, but it takes a little work. Assuming your data is dumped into column A, in column B do this B1: = A1 B2: =IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""), 0)),"", INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH( 0,COUNTIF(B$1:B1,$A$1:$A$2 0&""),0))) copy B2 down as far as you will ever need. It is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Create a dynamic name for column B (InsertNameDefine...) of say MyList with a RefersTo value of =OFFSET($B$1,,,COUNTA($B:$B),1) and use MyList in the DV. I have created a little example you can look at http://cjoint.com/?edlxqUTtnT -- HTH Bob Phillips (remove nothere from email address if mailing direct) "MarkN" wrote in message ... Thanks for the response Bob, I need something that will work for a generic list where unique items in column B populate the combo box. This is key, can I do this with data validation given that the list on sheet 2 is a dumped list where the values change 3 or 4 times a day? -- Thanks, MarkN "Bob Phillips" wrote: Use data validation rather than a combobox, as this uses a cell on the worksheet rather than an object off the worksheet. You can then use VLOOKUP formulae to look into your table, using the value selected from the DV cell. If using a range on another worksheet to populate the DV, name that range, and use that name in DV, else you will get an error. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "MarkN" wrote in message ... Simplistically, my problem is this: I would like to be able to put a combo box on sheet 1 that will allow a user to select a choice (the combo box should be populated with unique records from sheet 2, column b). Once a choice has been selected, all matching records from sheet 2 will be displayed on sheet 1. If anyone can point me in the direction of a similar example I would be grateful. -- Thanks, MarkN |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a dropdown option to display records
Good luck! You know where we are if you need further help.
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "MarkN" wrote in message ... Thanks Bob, I will get this working and then I will try to understand it! -- Thanks, MarkN "Bob Phillips" wrote: You can Mark, but it takes a little work. Assuming your data is dumped into column A, in column B do this B1: = A1 B2: =IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""), 0)),"", INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH( 0,COUNTIF(B$1:B1,$A$1:$A$2 0&""),0))) copy B2 down as far as you will ever need. It is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Create a dynamic name for column B (InsertNameDefine...) of say MyList with a RefersTo value of =OFFSET($B$1,,,COUNTA($B:$B),1) and use MyList in the DV. I have created a little example you can look at http://cjoint.com/?edlxqUTtnT -- HTH Bob Phillips (remove nothere from email address if mailing direct) "MarkN" wrote in message ... Thanks for the response Bob, I need something that will work for a generic list where unique items in column B populate the combo box. This is key, can I do this with data validation given that the list on sheet 2 is a dumped list where the values change 3 or 4 times a day? -- Thanks, MarkN "Bob Phillips" wrote: Use data validation rather than a combobox, as this uses a cell on the worksheet rather than an object off the worksheet. You can then use VLOOKUP formulae to look into your table, using the value selected from the DV cell. If using a range on another worksheet to populate the DV, name that range, and use that name in DV, else you will get an error. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "MarkN" wrote in message ... Simplistically, my problem is this: I would like to be able to put a combo box on sheet 1 that will allow a user to select a choice (the combo box should be populated with unique records from sheet 2, column b). Once a choice has been selected, all matching records from sheet 2 will be displayed on sheet 1. If anyone can point me in the direction of a similar example I would be grateful. -- Thanks, MarkN |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
dropdown with option | Excel Discussion (Misc queries) | |||
Dropdown box display only data dependent on another dropdown box? | Excel Worksheet Functions | |||
result of selecting from the dropdown list should be a dropdown list | Excel Worksheet Functions | |||
Navigating between option buttons is not selecting the option | Excel Programming | |||
Navigating between option buttons is not selecting the option | Excel Programming |