Selection list to bring up cells

Hi,

I have attached the test spreadsheet that my boss has sent me. Basically what he is trying to achieve is a report page, which when you use the drop-down list to select Customer Name + Customer Location (didn't know how to make both of them work), which would then return all of the data records that this customer has, taking from the data field.

Eventually he will want a graph to be made from those results, but that is less of an issue.

The issue is that I don't know how to make excel do this! In a database it would be so much simplier, but here I am completely stuck. Would appreciate any help you can give me.

Thanks,

Hello Toddneyx,

Welcome to the Forum!

You can also do this manually using Auto Filter.

1. Select the header row (row 2).
2. Under Data on the menu select Filter, Auto filter. Drop downs will appear above each column.
3. Click the drop down arrow over the column you want to sort the data by.
4. Choose from the list the item to use as the data filter.
5. Copy the filtered data (rows numbers will be in blue) using CTRL+C.
6. Paste them onto the report sheet using CTRL+V.

Sincerely,
Leith Ross

Hi guys,

Thanks for the information. My boss had already used the AutoFilter function, but wondering if there was something more straight forward for him.

Basically he wants to select the customer, and then below, all of the records under than customer should appear. I tried doing the vlookup myself and though this does give me part of the solution, I was wondering if there was anyway to fetch ALL of the records for that customer? Like in a database I could do this relatively simply with a query.

Hi,

Thanks alot for the help, that is exactly what I wanted the formula to do. If you don't mind, would you be able to explain step for step what the formula does?

=IF(ROW()-1>COUNTIF(Data!$B2:$B$1000,$B$2),"",INDEX(Data!$C$1:$C$1000,SMALL(IF(Data!$B$1:$B$1000=$B$2,ROW(Data!$B$1:$B$1000),""),ROW()-1)))

From my understanding, it takes the current row number, and calculates if this is greater than the total number of times the CustomerName appears in the data set. If it is true then it returns nothing. But if it isn't, then it fetches the specified row (e.g. C1) from the data set. Is this correct?

I have attached my modified version, just with some more fields and data to work with. I guess my main thing left to figure out, is how to filter these results even further. As you can see, a customer can have various different locations. My boss might want to focus in on just one customer account and just one of their locations. How would this be achieved?

The customer number is unique for each customer location, an autonumber from the SAP database. Would it be possible to select the customer number, but cloak it as the Customer Name + Customer Location? Sort of like a combo box in a database I am thinking.

Thanks again,

Originally Posted by ratcat

See "Ratcat Form" sheet. There is a dependent data validation at C2. There a hidden formula (A2) to help with location validation.

I do not understand this hidden formula.

=VLOOKUP(B2,List!$F$2:$G$5,2,FALSE)

It looks up the CustomerName (B2) but in F2:G5. I cannot understand how it knows which Customer Location to display from just that formula. Please can you explain as I have not managed to get this working on my real spreadsheet.

Originally Posted by ratcat

Am sorry I do not follow you here on what you would like to achieve.

Is it that instead of making two selection (Cust Name & Location), you would like to make one selection. Also which information is displayed ?

That's right, so the end user would select from the drop-down list, Joe Bloggs Plc America, but this is actually selecting customernumber 0000170168 in the background. It would then display the information (all the columns as before) for just that customernumber.