Filtering Results in List Box

[JohnS_323]

Hi all,

I'm trying to create a spreadsheet that will be used for performing physical inventory verification. The process is that users will go through their physical inventory and type in a batch number and a material code into a data entry tab in the spreadsheet. This will be compared to a separate tab that has what our system says we should have on hand. In some cases, a batch number can be used to make several material codes and the person doing the count doesn't know which one they have. In these cases, I would like for them to enter the batch number into column A of the data entry sheet and have a list box in column B be filtered to show them only the materials that have that same batch number in the current inventory tab. I'm not sure how to pass a parameter across from a cell to a list box in Excel. I looked into dependent list boxes but that misses the mark. I could do it fairly easily in Access but the user community that will be utilizing this spreadsheet isn't familiar with Access.

Thanks in advance for any recommendations.

 

[sxhall]

For the dependent dropdown method you need a list created for each possible list of codes based on the batch I believe.

This should work for you using the FILTER() function and then INDIRECT() in the name manager.

I've created a small sheet that when I type a batch in to cell A2 it will update column H 'Filtered List' based on the data I have in columns E and F for a list of values to show in the dropdown in cell B2!

Batch 301225 entered in cell A2...

Batch 300325 entered in cell A2...

In cell H2 I have just this formula entered in it. It will return all matches found from this one cell...

=IFERROR(FILTER(F:F,E:E=A2),"")

In the name manager I have the following formulaentered in...

=INDIRECT("Sheet1!$H$2:$H$" & COUNTA(Sheet1!$H:$H)+1)

I have set this with '+1' at the end to allow for a blank entry. This is a fixed reference though and should you change the lookup to another column then this will need to have that change made to it. It will not pick it up otherwise.

This way you only need one list that can be used in the dropdown box in cell B2.

Hope this is of help!