Filter Multiple Values

Question

I am using the filter function in cell F, if Cell E = a certain value. The issue is that sometimes the # entered in Cell E has multiple possibilities for cell F. Is there a way to work around it returning something other than a blank cell? Below is the formula I am using in cell F.

=IF(E4="","",(FILTER(MasterSheet!$A$1:$A$10000,MasterSheet!$C$1:$C$10000=E4,"")))

E is a customer #. F is the customer. G is the product. H is the amount.

Can F, G, & H all be drop downs?

So if E is Customer #123, I can select their name from a drop down in F, then select the product from a dropdown in G, then select the quantity?

Answer 1

At this point I am guessing at what is on MasterSheet...uploading a sanitized version might help, or at minimum a screen shot.

I modified the formula a bit to test on my own excel and it returned OK. I included four entries for 3001 and it all worked out OK.

Mike

Answer 2

I ended up modifying your formula to show below. However, sometimes in the Masters Page, say customer 3004, could have multiple entries.

The master page pulls from our outbound, manufacturing, and inbound tracking pages. So 3004, was brought in, manufactured, and exported back out.

There will also be multiple entries because 3004 could order multiple products.

In both of these situations, the formula returns blanks. That's what I don't want

=IF(A2="","",(FILTER(CHOOSECOLS(MasterCustomers!$A$1:$d$10000,2,3,4),MasterCustomers!$A$1:$A$10000=A2,"")))

Answer 3

To be clear, you want to enter in A2 a customer number, say 3004, and then have the spreadsheet return the customer name, each product on order and quantity for each?

If so this can be done by entering just the customer number in cell A2 and the filter function combined with CHOOSECOLS will return the customer name, product and quantity columns, however I need to know which column the in MasterSheet the customer number resides.

My answer below is wrong because you have not shared the customer number column yet, but I suspect the answer will be similar and is what would be placed in cell B2. This will spill into columns C and D so ensure those entire columns, and column B, too, are cleared of any formulas.

=IF(A2="","",

(FILTER(CHOOSECOLS(MasterSheet!$A$1:$D$10000,1,2,4),

MasterSheet!$C$1:$C$10000=A2,"")))

The CHOOSECOLS returns the first, second and fourth columns of MasterSheet. Note the bolded D column is changed from the C you had originally.

The bolded column reference in the final statement, MasterSheet!$C$1:$C$10000=A2,""))), need to be changed to whatever the column is that the Customer number is stored in.

Let us know how it works.

Mike

Answer 4

I want to be able type in a customers # and have their Name, product, and order quantity name auto populate. However, sometimes there are multiple entries for a customer #. It could be due to receiving or having multiple products. This example, Customer 3004 has multiple entries on the "MasterCustomers" Page because they have multiple products. They have 5 entries with 5 different order quantities.

Is there a way to have a drop down in each cell. So if I select Customer 3004 in cell A, there is a dropdown to select their name in B, a dropdown to select which product Im tracking in C, and D auto populates based off of A & C?

Formula entered into B

=IF(A2="","",(FILTER(MasterCustomers!$A$1:$A$10000,MasterCustomers!$C$1:$C$10000=A2,"")))

Formula entered into C

=IF(A2="","",(FILTER(MasterCustomers!$B$1:$B$10000,MasterCustomers!$C$1:$C$10000=A2,"")))

Formula entered into D

=IF(A2="","",(FILTER(MasterCustomers!$D$1:$D$10000,MasterCustomers!$C$1:$C$10000=A2,"")))

Answer 5

Hi,

I cannot understand your question. Share some data to work with, explain the question and show the expected result.