Dependent List maybe from VLookup

[wwilli41]

Hello everyone,

I am trying to setup a workbook for a coworker. I would like to have a list for one column named "Community". Then the next cell over have another dependent list that gives the option of "Premium Paid" (would be something like 99 or 199). Then I would need the next cell to populate the amount of coverage. Is there a formula like this?
I understand the basic concept of dependent lists and how I would need a master and list of dependents.
The problem I was most running into was how to make the list have multiple words.
If possible I would like to have the account have a drop down as well.

Sorry If this is not very clear. I really need some help.
I attached an Excel version of what I am trying to do.Example.xlsm

Thank you for Any help you can offer.

Sincerely,

Wes

[daffodil11]

Are you able to provide samples lists? If you have raw data to offer, I can show you how they fit together with an INDIRECT Data Validation.

For dependant lists, I prefer to use named ranges formatted as tables so that there's no need to account for the exact number of cells.

[wwilli41]

Here is the best I could come up with.
Please forgive my A.D.D. this morning.
Example.xlsm

Thank you,
Wes

[daffodil11]

There doesn't appear to be any values for Coverage in any of your tables. Is this something you want to autopopulate based on drop downs?

[daffodil11]

Here's your workbook with the three dropdowns.

I'm not sure how you want to populate Coverage.

Each list has been converted to a named range and table.

1. Account connects to hotel name
2. hotel name connects to premium based on a VLOOKUP that outputs to a table name of the same hotel, but without spaces (named ranges can't have spaces, so we use a go between to keep things pretty on the user end)

hotel dropdowns.xlsm

[wwilli41]

I cant believe I forgot that.
Thank you for continuing to help me.
I think this should be what you are looking for.
hotel dropdowns.xlsm

[daffodil11]

No problem. It helps keep this kind of knowledge fresh in my mind, I only get a data validation request once every couple of weeks.

For the Coverage I went with a SUMPRODUCT, although other array formulas or even INDEX(..MATCH( could do it as well.

First I selected A21:A56, hit F5, clicked Special, and chose Blanks. Immediately afterwards type = and click A21 and then hold Ctrl and hit Enter.
This little shortcut fills in all the blank cells with the value directly above.

The parameters:

SUMPRODUCT((range1=criteria1)*(range2=criteria2)*(range3))

For your case, we're searching A21:A56 for the hotel listed under Community, then of those matches we're looking at B21:B56 for Premiums that match the next selected drop down, and then outputting the Coverage that's associated.

Final formula looks like:

=SUMPRODUCT(('Raw Data'!$A$21:$A$56=Format!B4)*('Raw Data'!$B$21:$B$56=Format!J4)*('Raw Data'!$C$21:$C$56))

Here's the finished product:
hotel dropdowns.xlsm

[wwilli41]

Thank you! Your excel sheet works. But I am having problems with the creating list portion and I am not quite sure when and where to do the vlookup. Do I make the tables first?
So far I have made the whole raw data page with my real information but I am not sure how to get the lists to work. And how to with tables.
And mine will not do the dependent portion of the lists.
Any ideas?

[daffodil11]

1. Each list is converted to a table by highlighting the range including the header, and going to Insert-Table. Note: The headers don't do anything other than help remind you which table means what. They are there purely for organizational purposes.

2. Next, select the range not including the header, and go to the Name Box in the upper left corner under Clipboard and type in a name for that range. The premiums for The Westin should be named TheWestin with no spaces. This name is what comes out of the VLOOKUP and how it finds the data.

The first two steps will be repeated for every premium range.

The data validation for the premium lookup takes the output of the hotel name, VLOOKUPs The Westin to convert it to TheWestin, and uses INDIRECT to tell Excel that TheWestin should be evaluated not as a string, but instead as a named range.

The values in A11:A18 aren't contributing anything and you can delete those if you want to.