Question on Using a Drop down list to display an entire column of data

[Compuzed]

So. I am not a heavy exel user, but recently have taken on a role that could really benefit from a 'dynamic' checklist. Here is the Scenario. I check incoming contract renewals, and depending on the company that the contract is with, each has a differnent set of discounts that needs to be examined, to ensure that all the discounts that are applicable, are being credited.

For instance , Lets say I have 4 companies: Company 1, 2 , 3 and 4. I have a drop down list that allows the user to select one of the 4 companies. Then, depending on which company is selected via the dropdown, I want to display all the discounts associated with that company; one per line.
Company 1 may have 15 discounts ( ranging from loyalty to multi-product, etc) while Company 3 may have only 5.

I have tried going the route of data validation, but is seems to be lacking. After picking the first 'Company' value, then the resultant list will allow you to 'pick' one of the discounts from its own dropdown list, but this is not what I am after.
I am needing the "Company' choice to display the entire named range of company discounts.

At this point, I have named ranges for: Company, Company1Discounts, Company2Discounts, Company3Discounts, & Company4Discounts.

I am sure this must be fairly easy, but all of the references I have been able to find, do not display a resultant ENTIRE named range; rather just one of the values in the resultant named range.

I hope this makes sense; it can be challenging communicating effectivley without having a spreadsheet to point to.

Any help is appreciated

 

[Johnny C]

There are various ways of doing this.

The simplest explanation is probably here:-
http://www.contextures.com/newsletter/excelnews2018/20180206ctx.html
but there's a good chance you've already seen that.

to answer better, we'd need to know how your source tables for the discounts are laid out; have you got one company in a column with the discounts below? That would be the easiest option. Lets say they are on a sheet called DiscountData. Put the company ID's in Col A and use as the source of your dropdown list. Then put the company discount data next to each other, in say cols B-E, with the company name at the top in row 1 and the discounts below them but they should be in the column order that the companies are in col A - otherwise the formula is a little more complex

Then where you want to see them, put the dropdown list in your sheet. data validation is not the best way. In your Ribbon, right click and enable the 'Developer' ribbon. One of the buttons is called 'Insert' with an arrow to drill down to a list. Choose the 2nd option which shows the hint 'Combo box (Form control)'

Draw a combo box on your sheet. Right click for properties, choose 'Format Control'. In the input range, choose cells A1:A4 with the company names on the DiscountData sheet. In the Cell Link put a cell, lets say C2. I usually put the linked cell beneath the combo box. make it pretty with the 3D option and choose 4 rows to display if you want.

Then put the formula

=OFFSET(DiscountData!$A$1,$C$2-1,0)

in cell $D$2. Choose an option from the combo box and you should see the company ID appear in D2

Now in a table, put sequential numbers in col A from 1 to 15. (say start at row 10)
In Col B, put

=OFFSET(DiscountData!$A$1,$A10,$C$2)

and copy down. You might get a 0 if the cell is blank in DisCountData - you could put spaces in the cells in there or adjust the formula.

if for whatever reason you don't have the company data in column order that they are in the list, you would need this:-

=OFFSET(DiscountData!$A$1,$A10,MATCH($D$2,DiscountData!$B$1:$E$1,0))

so it looked the company ID up.

 

[Compuzed]

Thank you.
In the interest of moving forward, I temporilty developed a word doc per company that is about 2 pages long that I use as a checklist. However, now that I have seen this, I am going to resume work and try and make the spreadsheet work instead, as I think it is less cumbersome. Thanks for the reply !

 

[Compuzed]

Ha. I am so bad at this
I tried to follow along, and got as far as setting up the FORM CONTROL box.

Perhaps I could send you what I have in an offline email?

 

[Johnny C]

it would get stripped probably.

Have you got the Developer Ribbon visible?

The Office guide is pretty good.

https://support.office.com/en-us/article/add-a-list-box-or-combo-box-to-a-worksheet-in-excel-579e1958-f7f6-41ae-ba0c-c83cc6e40878


However ignore the Active X controls for now. Just choose the Combo Box in the Forms section, second left on top row, then draw a rectangle by left clicking and dragging in the worksheet. then right click to select Format and you can go from there.