Update dynamic drop-down list in excel without VBA

1
votes

I want to create 2 dependent drop-down lists in excel, first drop-down value decides what value has to be displayed for the second dropdown.

Used following OFFSET formula for second dropdown values to get populated dynamically.

=OFFSET($B$2,MATCH($G$3,$B$3:$B$17,0),1,COUNTIF($B$3:$B$17,$G$3),1)

. I have also prepared a sample file showcasing the formulas I have used.

When I select first dropdown value, second dropdown list gets updated. BUT, when I select another value in first dropdown, second list doesn't showcase the values immediately.

Sample file link - https://drive.google.com/file/d/1rwt6B-INgrQ0NgxIl-Nc8JeoGziBaiL2/view?usp=sharing

2
excelexcel-formula

2 Answers

2
votes

Now that's what I call a challenging request.
We will use the secret Evaluate formula to achieve this.

Disclaimer:

  • Ensure that a status never get separated from its peers (e.g. do not add another New at the end of the table, rather insert a column so that statuses stay in packs).
  • Note that changing status will not clear the definition cell. Simple VBA will do the trick (and that is the only place where you will need VBA).

Solution:

  1. Let's create a formula that returns a the address of the range we want to use in the dropdown. You can paste that it I3.
    =ADDRESS(ROW(OFFSET($C$2,MATCH($G3,$B$3:$B$17,0),0)), COLUMN($C$2)) & ":" & ADDRESS(ROW(OFFSET($C$2,MATCH($G3,$B$3:$B$17,0)+COUNTIF($B$3:$B$17,$G3)-1,0)),COLUMN($C$2))
    If I made no mistake with my French Excel, you should get the address of all the definitions for the status you put in G3. Extend the formula a few rows down and you will see it always matches G4, G5 and so on.

  2. Time to define a Name (Menu Formula > Name manager > New), that we will call DynamicRange.
    First, I would recommend to change the scope to your worksheet (Dropdown in the window).
    Next, put the very formula we tried before, but in an EVALUATE.
    =EVALUATE(ADDRESS(ROW(OFFSET($C$2,MATCH($G3,$B$3:$B$17,0),0)), COLUMN($C$2)) & ":" & ADDRESS(ROW(OFFSET($C$2,MATCH($G3,$B$3:$B$17,0)+COUNTIF($B$3:$B$17,$G3)-1,0)),COLUMN($C$2)))

Here what it looks like (except I'm all in French ...) New range created

  1. Go back to data validation and in the list's source, simply type =DynamicRange

The list is easy to define once the above was done successfully. Definition of the list

-1
votes

I see what you are trying to accomplish (get the second cell value to auto update to the first list entry), but I do not think it is possible without using VBA.

My understanding is that "Data Validation" only verifies the data within the cell, it does not change the existing value.

If you want to change the existing value, then I would look into VBA.