Multiple dropdown dependent lists in Excel

Question

So, I worked on this problem for a while and have not yet found an effective solution. I am trying to create three dropdowns. One for Discret , Block , and Village .

Here's how these categories relate to each other:

• There are many areas.
• In each area there are several blocks.
• Each block has several villages.

This is how my data is currently structured, I am open to suggestions and to another version. Currently, each district, block and village has its own combined (or one) cell, as you can see. All villages will occupy exactly one cell.

Another structure that I reviewed is given below. Maybe this will help, maybe it will not? This would help me if I used a VBA macro, but they don't seem to work in dropdowns.

So, this is my thought process, which I cannot convert to excel formula code, which works in the validation of drop-down list data.

• Get a list of all Regions. (Done!) - I just create a separate list for this and create a drop-down list.
• Get the range of all cells (since the drop-down list requires a comma-separated list or range), where the cell to the left of the current cell is the same as the cell selected in the drop-down list of the region.
• Repeat step 2 for the village and block name.

I believe that I can create a number of named lists districts + blocks to achieve this using the INDIRECT() function. But I'm not sure if this is the best way.

All solutions are welcome! It should work in Excel 2003 and above. Thanks!

Update: I want the drop-down lists (district, block, village), one in each row . This is a big data entry sheet that I am doing.

Thanks!

EDIT: districts+blocks not districts*blocks (Thank god)

Answer 1

Given that the question has changed halfway, here is another answer based on my first.

Check this file . It is based on the above solution, but has a mini macro that provides the current line number in the name "CurrentRow". I use this name to extract the region / block of the current line and create drop-down lists based on this.

I almost got this to work without a macro by providing the formula =ROW(INDIRECT(CELL("address"))) called "CurrentRow" - but then you will need to manually press F9 (to update the calculation) before you click on drop-down list...

Answer 2

Here is another version using Dynamic Named Ranges

Set up your validation lists as follows

Add named ranges as follows

 Name Range DistrictList =Sheet4!$A:$A BlockList =Sheet4!$C:$C VillageList =Sheet4!$G:$G SelectedDistrict =Sheet4!$J$2 SelectedBlock =Sheet4!$K$2 SelectedVillage =Sheet4!$L$2 rDistrict =OFFSET(DistrictList,1,0,COUNTA(DistrictList)-1,1) rBlock =OFFSET(OFFSET(BlockList,0,1,1,1),MATCH(SelectedDistrict,BlockList,0)-1,0,COUNTIF(BlockList,SelectedDistrict)) rVillage =OFFSET(OFFSET(VillageList,0,1,1,1),MATCH(SelectedBlock,VillageList,0)-1,0,COUNTIF(VillageList,SelectedBlock)) 

Valid data entry in cells J2 , K2 , L2 : apply List check using the above formulas.
Now pop-ups will depend on previous choices.
Please note that data entry cells must not be on the same sheet as validation lists.

Answer 3

To verify the data you need to use the named areas . Here is an example file that demonstrates the approach: https://www.dropbox.com/s/4fy9b34txglmy4r/LinkedDropdowns.xlsx

Named scopes can be set via Formulas > Name Manager .

Answer 4

You can solve this problem with dynamic named ranges and some advanced formula logic. Since this is quite difficult to explain, here is an example sample file . All you have to do is paste your data into the AC columns - and copy the formulas in the DJ columns to the place where your data ends! :-)

Here is my attempt to explain this:

The idea is as follows:
Step 1. For each set of elements you have a counter (= ID), which increases only if it belongs to the selected parent (s) and is unique
Step 2. Use INDEX / MATCH for this identifier to get the name
Step 3: Create a dynamic named range using OFFSET and COUNT to create a range for the dropdown Step 4: Convert the selected option back to identifier using the MATCH function and as a source for step 2 for “children”

In detail, this means:

• The source data should resemble your structure, so don't explain anything
• Areas
  • Cell D3 = 1 (this is the starting identifier)
    The formula in D4 is as follows:

      = IF (AND (A4 <> A3, A4 <> ""), D3 + 1, D3) 

    This will increase the number of a new, non-empty item, will be found in column A
    The formula in M5 =MAX(D:D) (this is a graph of areas).
    Copy the formula in N5 and M5 for the other elements.
  • Prepare a list of consecutive identifiers in column G (I just used =G3+1 in G4
    Fill column H with this formula:

      = IF ($ G3 <= M $ 5, INDEX (A: A, MATCH ($ G3, D: D, 0)), "") 

    (The formula really works for all three columns, so just fill in the HJ columns directly)
  • Paste the new name (Formulas-> Name Manager-> Paste). Name it "Regions" and specify the following formula: =OFFSET($H$2,1,0,$M$5,1) . This will create a range of sizes CountOfDistricts (= M5) x 1, starting from 1 line below H2 - that is, it will refer to H3: H5 in the example.
    Apply data validation to cell M10, use the =Districts formula as the source for the list.
    Select an area to check.
  • Cell M6: =MATCH(M10,Districts,0)
• Blocks:
  • Cell E3 = 1
    E4 and as follows:

      = IF (D4 = $ M $ 6, IF (AND (B4 <> B3, B4 <> ""), E3 + 1, E3), E3) 

  • Already done in step 2 above
  • Blocks name: =OFFSET($I$J,1,0,$N$5,1) , select the block
  • G4: =MATCH(M11,Blocks,0)
• Villages:
  • Cell F3 = 1
    F4 onwards:

      = IF (AND (D4 = $ M $ 6, E4 = $ N $ 6), IF (AND (C4 <> C3, C4 <> ""), F3 + 1, F3), F3) 

  • Already done in step 2 above
  • Village Name: =OFFSET($J$2,1,0,$O$5,1)
  • G4: =MATCH(M12,Villages,0)

Done !:-)