Populating List boxes

[TheOddGirl]

Hi all,

I have two list boxes and I want to fill one list box with the contents where the contents contain a unique ID from the other list box. Example list box1 contains ShopID and Shop Name and second list box would be populated with the ShopID and more details from the data in the spreadsheet. How can this be done?

Thanks..

 

[Dan5977]

Hi all,

I have two list boxes and I want to fill one list box with the contents where the contents contain a unique ID from the other list box. Example list box1 contains ShopID and Shop Name and second list box would be populated with the ShopID and more details from the data in the spreadsheet. How can this be done?

Thanks..

So I'm assuming you can handle box 1 easily enough with data validation.

For box 2 you can use named ranges and data validation.
So if you have a range containing all of the selections you want for ShopID, select them and then in the box that shows the cell selection (directly above A1) type in ShopID. You have now created a named range. Do the same for ShopName (you cannot have a space in a named range).

Let's assume that your box 1 selection is in cell A2 and that you want your second box in cell B2.
click on cell b2 and go to data validation, then allow list and for source type =INDIRECT(A2)

That should do it.

 

[TheOddGirl]

So I'm assuming you can handle box 1 easily enough with data validation.

For box 2 you can use named ranges and data validation.
So if you have a range containing all of the selections you want for ShopID, select them and then in the box that shows the cell selection (directly above A1) type in ShopID. You have now created a named range. Do the same for ShopName (you cannot have a space in a named range).

Let's assume that your box 1 selection is in cell A2 and that you want your second box in cell B2.
click on cell b2 and go to data validation, then allow list and for source type =INDIRECT(A2)

That should do it.

Hi, thanks for the reply. I probably didn't explain it very well

I have in one Tab1
ShopID

In another Tab2, I have

ShopID, Goods sold Desciption.
A1, Sweets
A1, Paper
A2, White Goods


I have populated one listbox with the contents of Tab1 (ShopID) what I need it to do is. When I select ShopId from the first listbox the second listbox will be filled with the data from Tab2 where the ShopID matches

Thanks

 

[Dan5977]

In that case you can do the same thing but will need extra named ranges.

You will need separate lists for A1, A2, etc. You can then make them each named ranges and use the INDIRECT data validation as described above.

Pretty sure you can't name a range as A1, A2, etc. though as they are cell references. 2 ways around this - 1) change ShopID to ShopA1, ShopA2, etc. (or another usable name). 2) Create a helper column that can be hidden and contains a different name - you could put a formula in it such as ="Shop"&A1 - your INDIRECT function would then refer to this hidden helper column.