Multiple Dropdown Options based on one Entry

1

If you look in Column A - You will see a policy number in cell A2. Next to it you will see two comments that both apply to the policy (Cells B2 and B3). 

How Do I make cells (bs & b3) just once cell with multiple drop down options?

And also look at cells B5&6 where they are the same comment. Is there a way to isolate those or do a formula for all comments that are the same for one status only?

I need to be able to have a status for each policy - but i'm hoping you can help with this because it's very difficult to give one policy multiple status's for reporting. 

Hope you can help - thanks!

Answer
Discuss

Answers

0

Yours is a wonderful question because I enjoyed creating a new solution based on what I learned myself since I did the same job years ago.

Start with understanding ranges. In the attached workbook (a copy of the one you posted) I inserted a sheet called Lists. Select B1:B9 on this sheet and look at the Name bar (above A1). You will see the word Comments displayed there. This means that the range $B$1:$B$9 was named as "Comments". You can refer to it by either $B$1:$B$9 or Comments anywhere in your workbook. The difference is that "Comments" explains what the list holds but that isn't all. In the future, when you change the range address to, say, $B$2:$B$14 you would need to change all references in all your formulas if you used the address. But if you used a name the new address is given in the Name Manager (on the ribbon's Formulas tab) and all formulas throughout the workbook are updated automatically.

To give a name to a range select the range and type the name in the Name Bar (no blanks allowed in the name). To change or delete a name already given use the Name Manager. In the Name Manager you can assign names to be visible throughout the workbook (default) or only on specific sheets.

Now, as you see, the range Comments has a list of all the comments you might ever need. You can expand or shrink the list and modify each item in it at any time.

Now select C2 on the MULTIPLE LOANS sheet, click Data Validation on the ribbon's Data tab and select Data Validation. You will see that only items from a List are allowed and that the Source of the List is =Comments. You might write =Lists!$B$1:$B$9 instead but then you would need to modify the formula each time you make changes to the Comments range. Explore the validation dialog. You can determine if the cell can be left blank by the user or if an instruction is to be displayed when the cell is seleected. Note especially the instruction on the Error Alert tab. By default "Show error alert when invalid data is entered" is checked. You can specify a message to be shown or just use the default. But if you want to allow the user to enter a comment which isn't in the list you should uncheck this box.

Now, the above answers your question, I believe. Just copy C2 down as far as you need. However, I thought that your comments tend to be rather unwieldy. I thought of splitting them into two columns, with a status being selected in C2 and a further comment in E2. The available comments in E2 should be different depending upon the selected status. Select "Surrender" in C2 or "In arrears" and the dropdown in E2 changes. Note that the already selected text doesn't change even as the dropdown list does. That's the way Excel wants it. Not my doing. The lists are taken from the named ranges on the Lists tab. I called them Surrender and Arrears and you can modify them as you require (see above). There is also a range called Default the use of which will become apparent momentarily.

When you select E2 and click Data>DataValidation>Data Validation you will see that the Source is specified as =INDIRECT($D2). This means that D2 contains the name of the named range from which the dropdown takes its list. Unhide column D to see how that works. You will see the formula below in D2. Note that you can copy it down as far as needed.

=IFERROR(INDEX({"Surrender","Arrears"},MATCH($C2,{"Surrender","In arrears"},0)),"Default")

If you wish to deploy this system you should understand this formula because you will need to adapt it. Read the formula like an onion, peeling one layer off at a time. On the outside you have =IFERROR([bla bla bla],"Default"). This means that if the selection made in C2 can't be identified by [bla bla bla] the "Default" list shown be used. That list is currently blank. Note that I removed the check against "Show error alert when invalid data is entered" in E2 so that the user can write what he wants in that case.

Now about the bla bla bla. That is an INDEX function. Very simple here! It says Select the nth item from the list. The "List" is surrounded by curly braces: {"Surrender", "Arrears"}. These are the names of the named ranges, each name within its own quotation marks and separated by commas. To familiarise yourself, type =INDEX( {"Surrender", "Arrears"}, 1) in any cell and the cell will display "Surrender". Type 2 in place of 1 and the display changes to "Arrears". Add more items and call them up by changing the [row] number.

In the bla bla bla part of the formula of D2 the number is found by the MATCH function. The MATCH function is easy, too. it says Tell me the position of the word X in the following array. Type =MATCH("In arrears", ,{"Surrender","In arrears"}, 0) in any cell and the answer will be 2 because "In arrears" is the second item in the array. Type  =MATCH("Surrender", ,{"Surrender","In arrears"}, 0) and the answer will be 1. Add more elements to the array. If you ask for an element which doesn't exist this formula will return an error. Remember that the outside layer of the onion in D2 instructs what to do when that happens.

Now, as you see MATCH in D2 uses $C2 as the criterium to find. Therefore the MATCH array contains "In arrears" which may be selected in C2 but the formula will return "Arrears" which is the name of the named range.

To sum it all up, list all the named ranges in the INDEX array and all the selectible texts from the Comments range in the MATCH array. Make sure that the positions are matching in both arrays.

As an improvement of the above, the formula I inserted in D10 could be used. It is built on the fact that the MATCH array is identical to the Comments list.

=IFERROR(INDEX({"Surrender","Default","Default","Default","Default","Default","Default","Default","Arrears"},MATCH($C10,Comments,0)),"Default")

Observe that the number of elements in the INDEX array must be equal to the number of cells in the Comments range. I just re-used the same Default range for all the choices. You would probably create a custom list for each.

The non plus ultra in this series is the formula below. You will find it demonstrated in D20.

=IFERROR(INDEX(Ranges,MATCH($C20,Comments,0)),"Default")

In this formula the INDEX range is a named range itself, meaning you can do all your list management from the Lists sheet, modifying, adding and deleting without ever having to touch the MULTIPLE LOANS sheet or the formulas in it. All you need to observe is that the ranges you list in the Ranges list really exist and comprise of the intended comments.

You may like to hide the Lists sheet so that nobody fools around with your ranges. But now, at final last, let's put some icing on the cake. This comes in the shape of an Excel rule by which the formula in the Validation Source field can't contain arrays. That's why D2 and D10 are needed. In D20 arrays are both replaced with ranges. Therefore the formula of D20 can be incorporated in the Data Validation Source. This is the formula used to specify the dropdown in E30.

=INDIRECT(IFERROR(INDEX(Ranges,MATCH($C30,Comments,0)),"Default"))

The result is exactly the same as in row 20, in fact all the previous, but the invisible column is no longer needed.

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login