Choose customer; only show non-blank boxes

[LaurenSmalley]

I am making a spreadsheet to show all of the criteria each of our customers require on their Sales Order. Not all customers require the same info. I wanted to know if there is a way to choose one of the customers, and then show only the fields they require. Also, (if possible, but not absolutely needed) is there a way that when I choose the customer all of the required fields can be listed in 1 column rather than all the way across the columns? My spreadsheet is attached; sheet 1 is the beginning of my data and sheet 2 is how I would like it to look when I choose a specific customer.

[Cutter]

In Sheet2 cell B2 paste this formula:

=VLOOKUP($B$1,Sheet1!$A$2:$M$45,MATCH(A2,Sheet1!$A$1:$M$1,0),0)&""

drag it down to B8

[LaurenSmalley]

Ok, but is there a way to have a drop down list to choose any customer number from sheet 1?

[Cutter]

Sorry, missed the first part of your question.

Select the cells on Sheet 1 and click in the name box (at left of formula bar)
Type Customers and hit Enter
Now you can go to Sheet2 and select B1
Add Data Validation to that cell using a List
Use Customers as the source for that list

You will now have all your customer ID#'s in a dropdown that you can choose from

Here's a link for more info on Named Ranges: http://www.contextures.com/xlnames01.html

[NBVC]

In B2:

=INDEX(Sheet1!$B:$M,MATCH($B$1,Sheet1!$A:$A,0),MATCH($A2,Sheet1!$B$1:$M$1,0))

copied down.

[LaurenSmalley]

It is giving me formula errors. If I attach my spreadsheet, can you do it and send it back?

[Cutter]

I fail to see how that would help you to learn.

What error(s) are you getting and with what formula?

[LaurenSmalley]

When I do the data validation, the only option from the drop down menu is "CUSTOMERS". Am I supposed to select the whole table (including column titles) or just the data?

The formula errors is talking about circular references.

[Cutter]

Post your worksheet and I will tell you what you have to correct.

[LaurenSmalley]

Cutter, I didn't realize that NBVC posted that formula. I left that out and only did the data validation...but still not working.

I will not reply for an hour...lunch break.

[Cutter]

Here you go.

[NBVC]

FYI, my formula did work based on the sample you posted... just need to add the &"" to the end, for those cells that have no matches to return.

[LaurenSmalley]

Thank you both for your help.