Set up dependent drop-down list for multiple rows in Excel
by | updated on November 1, 2021
If you are looking to create an expandable multiple drop-down list that can be dragged or copied across multiple rows, you've landed on the right page and will have the solution in minutes.
The introduction of dynamic arrays has made our lives as Excel users a lot easier. Among many other things, they significantly simplify the creation of dynamic drop down lists. The above-linked tutorial shows how to quickly set up a multiple drop-down with dynamic arrays and make it expandable to automatically include new entries.
I felt very proud of the work we'd done until we got a few comments like this: "That works great for one row, but how to get this to work for the entire column?" A fair question. Indeed, Excel picklists are most often used in worksheets with hundreds of rows to facilitate data entry. So, we reconsidered the whole approach, worked out a formula that automatically adjusts for each row, and I'm thrilled to share this new solution with you!
- Source data
- Structure the source data
- Make primary drop-down
- Create dependent drop-down list
- Copy drop down lists across multiple rows
- Result: dependent drop down in multiple rows
- Make an expandable multiple drop-down list
Source data
For starters, let's get some source data to work on. In our main table on the left, we want to have two picklists in each row, so that selecting a Dept. in the first list only displays Managers for that selected department in the second list. If you change the selection in the main list, the names in the dependent drop-down will update accordingly.
Steps 1. Structure the source data
The source data for drop-down lists often come from different sources and are organized differently. So, our first step is to structure the original data according to our needs. For this, we are going to create some sort of preparation table that will list all different department names in the header row, and under each dept. name there will be a list of employees working in that specific department. To automate the work and prevent human errors, we will be using the following formulas.
To get the departments, enter this formula in H2.
=TRANSPOSE[SORT[UNIQUE[E3:E15]]]
Here, the UNIQUE function extracts all the different departments from E3:E15. The SORT function arranges the results in alphabetical order so that the items of your main list will be sorted from A to Z [if you don't want that, you can remove SORT from the formula, and the dept. names will appear in the same order as in your source table]. Finally, TRANSPOSE changes the output orientation from vertical to horizontal.
Please notice that the formula needs to be entered just in one cell, and the results spill into neighboring cells automatically [this feature is called a spill range].
This way, we've got the items for our main drop-down list:
To pull the manager names, the formula in H3 is:
=SORT[FILTER[$F$3:$F$15, $E$3:$E$15=H$2]]
Here, we utilize the FILTER function to filter the employees that belong to a particular department [$E$3:$E$15=H$2]. The SORT function arranges the names in alphabetical order [if you wish to preserve the existing order, remove it from the formula].
Like the previous formula, this one is also dynamic, and the whole spill rage is filled with the results at once. All you need to do is to drag the formula to the right to get a list of managers for each dept.
And this gives us the items for the dependent drop-down list:
- In this example, we have all the data on the same sheet to make things easier for you to follow. In your real spreadsheets, you'd better place a preparation table on a separate sheet to make sure there are enough empty cells down and to the right to accommodate all the data. As already mentioned, dynamic arrays formulas are entered only in one cell, and you cannot know how many cells the results will spill into.
- If your goal is to make expandable drop-down lists, then use a slightly different method for the preparation table, which is discussed in this example.
Step 2. Make a primary drop-down list
With the source data properly arranged, create the first drop-down list in the usual way with the help of Excel Data Validation:
- Select the topmost cell in which you want the primary dropdown list to appear [B3 in our case].
- On the Data tab, in the Data Tools group, click Data Validation.
- On the Settings tab of the Data Validation dialog box, configure the rule:
- Under Allow, select List.
- In the Source box, enter the spill range reference pointing to the departments names in the preparation table: =$H$2#
This syntax [cell address followed by a hash tag] refers to the entire spill range no matter how many cells it actually contains.
- Click OK to close the dialog.
The primary drop-down list for the first cell is done:
Step 3. Create a dependent drop-down list
Technically, setting up a successive picklist seems pretty easy - you just need to create one more data validation rule for another column. However, there's a tricky part - the formula for the Source box. This time, you cannot use a normal spill range reference because the second drop-down list needs to take into account the selection in the first drop-down. To solve this need, we'll be referring to a relevant spill range indirectly with this formula:
Where:
- row_num - the number of the row in the preparation table in which the formula returning the items for the dependent dropdown resides. Our formula is in row 3 [H3:K3], so it's 3. Instead of hardcoding the row number, you can use the ROW[] function, but we won't complicate things more than they already are.
- preceding_col - a cell reference to the column preceding the leftmost column of the preparation table. In this example, the preparation table begins in H2. The preceding column is G, so we use COLUMN[G1]. Here, only the column coordinate matters, the row number can be any.
- dropdown_cell - is the address of the topmost cell containing the primary drop down list, B3 in our case. Please be sure to use a relative cell reference without the $ sign, so that the formula adjusts correctly for each row where you'll copy your drop-down.
- dropdown_spill_range - the reference to the spill range in the preparation table containing the items for the primary drop down list. In our example, it's the departments returned by the dynamic array formula in H2, so we use this spill range reference: $H$2#
Putting all the above pieces together, we get the following formula for the dependent drop down validation rule:
=INDIRECT[ADDRESS[3, COLUMN[G1] + MATCH[B3, $H$2#, 0], 4] & "#"]
Now, simply select the topmost cell for the secondary drop-down list [C3], go to the Data tab > Data Validation, and configure the rule like this:
That's it! The dependent drop-down list is good to go:
=INDIRECT["Sheet1!"&ADDRESS[3, COLUMN[G1] + MATCH[B3, Sheet1!$H$2#, 0], 4] & "#"]
In the above formula, simply replace Sheet1! with the name of the worksheet where your source data and the preparation table are kept.
How this formula works:
From my experience, many of our blog visitors are eager to know not only how to apply this or that formula but also understand its internal logic. For such curious and thoughtful users, I'm providing the detailed break-down of the formula:
=INDIRECT[ADDRESS[3, COLUMN[G1] + MATCH[B3, $H$2#, 0], 4] & "#"]
On a high level, we utilize the INDIRECT function to "dynamically" refer to the spill range of managers corresponding to the department selected in the primary dropdown.
Suppose you picked Planning from the drop-down in B3. The employees of the Planning dept. are listed in column J beginning in J3. That means, we need to somehow supply the J3# reference to INDIRECT, and here's how we do it:
To get the column letter, the following 3 functions are used together:
ADDRESS[3, COLUMN[G1] + MATCH[B3, $H$2#, 0], 4]
The COLUMN function returns the column number of G1, which is 7.
The MATCH function searches for the B3 value in list of departments beginning in H2 and return its relative position, which is 3 [Analysis, Design, Planning, ].
Adding up these two numbers gives us 10, so the ADDRESS function takes this form:
ADDRESS[3, 10, 4]
Where 3 is the row number, 10 is the column number and 4 is the relative cell reference type. The result is the "J3" reference that goes directly to INDIRECT :] and reduces our tricky formula to an easy understandable one:
INDIRECT["J3"&"#"]
Concatenating a cell reference with a hash tag produces a spill range reference, so that INDIRECT returns an entire array rather than a single cell value. Done!
The beauty of this formula is that it works perfectly for a single-letter, two-letter and even tree-letter column [thank you Jonathan for pointing us in the right direction!].
Step 4. Copy drop down lists across multiple rows
This is the easiest part. For the picklists to appear in multiple cells, you can simply copy them like any other cell content by using the Copy / Paste feature or by dragging through the adjacent cells. For practice, we will try both techniques.
To copy the primary drop down list, select the cell containing it [B3] and drag the fill handle [a small square at the lower right-hand corner of the cell] through as many cells as needed:
To copy the dependent drop down list, let's do a regular copy/pasting:
- Select the cell with the secondary drop-down [C3] and press Ctrl + C to copy it.
- Select all other cells where you want the dependent list to appear [C4:C12] and press Ctrl + V to paste the copied contents.
Now, both picklists appear in every row letting you select a department and manager for each project.
Please keep in mind that the above methods copy all the contents of a cell including Data Validation and the current selection. So, they are best to be used when no item has been selected in the list yet.
If you've already selected the items, then you can copy only the Data Validation rule by using the Paste Special feature. Here's how:
- Select the drop-down cell and press Ctrl + C or click Copy on the ribbon.
- Select the destination cells.
- Right-click the selected range, and then click Paste Special .
- In the Paste Special dialog window, choose Validation and click OK.
Result: multiple rows dependent drop down list
Though our dynamic arrays solution is not a one-click setup, it's a way faster than it looks and definitely much faster than the old-fashioned approach with named ranges. Anyway, the result is worth the effort, agree?
Here it is - the dependent drop down for multiple rows where selecting an item from the first list determines which items will appear in a secondary drop-down.
How to make multiple drop-down list expandable
If more data is likely to be added to your source table in the future and you wish to have new entries included in the drop-down lists automatically, then you will have to use a bit more complex versions of the formulas for the preparation table. There are 2 possible approaches here, a regular range and a full-fledged Excel table, each having its own caveats.
Approach 1. Organize source data in a table
One of the key advantages of Excel tables is automatic expansion to accommodate new data, and we are going to leverage this ability.
The first thing you do is convert source data to a table. The quickest way is to select the range E2:F15 and press Ctrl + T. For convenience, we name the table Source_data.
Next, build the formulas for the preparation table by using structured references. If you are not familiar with this syntax, it's not a problem! Just start typing a formula in a cell, select the required range in your table, and Excel will create an appropriate structured reference for you automatically based on the column names.
To extract the departments, the formula in H2 is:
=TRANSPOSE[SORT[UNIQUE[Source_data[Dept.]]]]
To get the manager names, enter the below formula in H3 and drag it to the right through a few more cells than there currently departments [say, through P3]:
=IFERROR[SORT[FILTER[Source_data[[Manager]:[Manager]], [Source_data[[Dept.]:[Dept.]]=H$2]]],""]
Play pay attention that absolute structured references should be used for the Dept. and Manager columns so the references won't shift when the formula is copied horizontally. The default in Excel tables is a relative column reference such as table[column]. To make the reference absolute, you need to repeat the column name like this: table[[column]:[column]].
The IFERROR function is used to prevent errors when the formula is copied to extra columns to the right.
If all done correctly, the preparation table expands automatically to incorporate new records:
Approach 2. Use a range but improve formulas
If the table references look too complex to you, you can do with a normal range too. In this case, a couple of improvements need to be made to the formulas:
- Include some empty rows in the ranges supplied to both formulas.
- Filter out blank cells so they won't get into your picklists.
To extract the department names, the formula in H2 is:
=TRANSPOSE[SORT[UNIQUE[FILTER[E3:E30, E3:E30""]]]]
Please pay attention that we use the range E3:E30, though currently there are far fewer records in our source table.
To get the manager names, enter the below formula in H3 and drag it through a few more columns than there are currently entries in the header row:
=IFERROR[SORT[FILTER[$F$3:$F$30, [$E$3:$E$30=H$2]*[$E$3:$E$30""]]],""]
In this case, we lock the range references with the $ sign to prevent them from changing while copying the formula.
As with the previous solution, the new records appear in the preparation table once they are added to the source table.
Result: expandable multiple drop-down list
Whichever approach you opted for, configure and copy two Data Validation rules like explained in Steps 2 - 4 above, and the newly added items will be displayed in the drop-down lists without any extra effort on your side!
That is my go-to for making a multiple drop down list in Excel. Hopefully, this solution will be useful for you too. Anyway, I thank you for reading and hope to see you on our blog next week!
Practice workbook for download
Multiple rows dependent drop-down [.xlsx file]
You may also be interested in:
- Creating a simple drop down list in Excel
- How to make a dependent drop-down in Excel 2019 - 2007
- Create a dynamic dependent dropdown an easy way
- Using Data Validation in Excel
Excel: featured articles
- Merge multiple sheets intoone
- Combine Excel files into one
- Compare two files / worksheets
- Merge 2 columns in Excel
- Compare 2 columns in Excel for matches and differences
- How to merge two or more tables in Excel
- CONCATENATE in Excel: combine text strings, cells and columns
- Create calendar in Excel [drop-down and printable]
- 3 ways to remove spaces between words in Excel cells
Table of contents
48 comments to "Set up dependent drop-down list for multiple rows in Excel"
- Jeff says:September 20, 2021 at 5:52 pm
I am struggling with this formula once I modify it to use a Preparation Table on a different tab.
I am using your example document and am getting the same errors as with my own data.
- To test the formula using a Prep Table, I copied the "Source Data" and "Preparation Table" data to a new sheet called "DATASET" and made sure "Source Data" started in Column E.Your original formula: =INDIRECT[ADDRESS[3, COLUMN[G1] + MATCH[B3, $H$2#, 0], 4] & "#"]
Your recommended change to use the Prep Table on a different tab:
=INDIRECT["Sheet1!"&ADDRESS[3, COLUMN[G1] + MATCH[B3, Sheet1!$H$2#, 0], 4] & "#"]Modified to use a Prep Table on another sheet called "DATASET":
=INDIRECT["DATASET!"&ADDRESS[3, COLUMN[G1] + MATCH[B3, DATASET!$H$2#, 0], 4] & "#"]This reports back: 0
Any idea why this is not working as expected?
Reply- Alexander Trifuntov [Ablebits Team] says:September 21, 2021 at 7:49 am
Hello!
I used your DATA VALIDATION formula in cell C3 and didn't get the error.Reply - Jeff says:September 22, 2021 at 12:44 pm
It took some time, but eventually I fully understood how the formula is working which helped me get the variables corrected. The instructions detailed are accurate, I just had to get my brain wrapped around it all.
Any advice on if I have three columns of source data??? :]
Reply- Alexander Trifuntov [Ablebits Team] says:September 22, 2021 at 1:26 pm
Hello!
Please check out this article to learn how to create a multiple dependent dropdown in Excel.Reply
- Ivor Hartley says:September 11, 2021 at 6:01 pm
Mark Seely says:
March 6, 2021 at 1:27 amHi Alexander,
The article you reference is one that shows how to do multiple dependant drop downs for one row, and this article shows you how to create one dependant drop down for multiple rows. Now, how do create multiple dependant drop downs for multiple rows?
I have the same problem is there a solution. I need to create multiple dependent drop down lists for multiple rows - possible surely.
Exellent articles btw.
Regards,
Ivor
Reply - Ben says:August 18, 2021 at 8:36 pm
Hi!
Great tutorial! I am now trying to add another dependent column to this. Any suggestions on how to proceed?
Thanks!
Reply - Lee Quinn says:August 15, 2021 at 2:06 am
hi guys, thank you very much for this, hoping you can help me to expand on the concept please?
what i'm looking for is multiple dependent drop downs with a look up [i'll comma separate here just to show values]:
region, tower, practice, role, rate
au, tower1, practice1, senior role, $100
au, tower1, practice1, mid role $75
au, tower1, practice1, role $60au, tower1, practice2, senior role, $100
au, tower1, practice2, mid role $75
au, tower1, practice2, role $60au, tower2, practice1, senior role, $100
au, tower2, practice1, mid role $75
au, tower1, practice1, role $60au, tower2, practice2, senior role, $100
au, tower2, practice2, mid role $75
au, tower2, practice2, role $60us, tower1, practice1, senior role, $100
us, tower1, practice1, mid role $75
us, tower1, practice1, role $60us, tower1, practice2, senior role, $100
us, tower1, practice2, mid role $75
us, tower1, practice2, role $60us, tower2, practice1, senior role, $100
us, tower2, practice1, mid role $75
us, tower1, practice1, role $60us, tower2, practice2, senior role, $100
us, tower2, practice2, mid role $75
us, tower2, practice2, role $60thanks in advance!
Reply - Jen says:June 11, 2021 at 2:57 pm
Yeah my explanation of adding a none in the second drop down, obviously made no sense ?. But I have sorted it now by adding one none row in the source data and then in the preparation table I put this: =IFERROR[SORT[FILTER[Sheet1[[Name]:[Name]], [[Sheet1[[Type]:[Type]]=AA$1]+[Sheet1[[Type]:[Type]]=" None"]]]],""]
Reply - Jo says:June 10, 2021 at 9:20 pm
This is exactly what I need. But in my excel version, the functions Filter and unique are not working. any workaround for this, please?
Reply- Svetlana Cheusheva [Ablebits Team] says:June 11, 2021 at 8:35 am
Hi Jo,
FILTER and UNIQUE are the so-called dynamic array functions and they are only available in Dynamic Array Excel included with Microsoft 365 subscriptions.
In earlier versions, you can use one of the approaches described in How to make a dependent drop-down list in Excel 2019 - 2010.
Reply
- Jens says:June 4, 2021 at 12:27 pm
Hi,
I have my source data and preparation data on a different sheet, I've read the tip at the top about how to get this to work with that, but I can't for the life of me figure out what I've done wrong?
I have my equivalent data like this:
Data sheet called Adresseliste:
Dept in column A2:A78 [Named Postnummer]
Preparation data in i D:CB [varying row length, data starts from row 2]Sheet with dropdown I call my data like this:
C2 Data validation with column with a list =Postnummer
D3 should then be dependent on C2 for the options I can pick: =INDIREKTE["Adresseliste!"&ADRESSE[2; KOLONNE[Adresseliste!C2] + SAMMENLIGN[C2; Adresseliste!$D$1#; 0]; 4] & "#"]//1drv.ms/x/s!AlfA0KbCw_8kg9IpkGxrYDXxVAMUhA?e=fTud37
Apologize for the danish, but I've added a workbook as well.
Reply- Luis says:October 7, 2021 at 9:26 pm
I realized that when you're working with excel in a different language, the sheet referencing can be a bit different.
In my case, Spanish, I had to reference my sheet as 'MASTER DATA'! so I ended up with "'MASTER DATA'!"
My formula in Spanish:
=INDIRECTO["'MASTER DATA'!"&DIRECCION[83,COLUMNA[A1]+COINCIDIR[H43,'MASTER DATA'!B82#,0],4]&"#"]I don't know in danish but you could try changing it.
Reply
- Prashant says:May 25, 2021 at 5:54 am
hi,
I have a different query, What if i want multiple secondary drop-down based on one primary drop-down input.
For EG. from the first [primary] drop-down, we select a country. Then other drop down lists Languages, Cities, rivers, etc should be visible for only that country in three different cells. So defining multiple ranges with the same name [country].
Reply - Jen says:May 24, 2021 at 5:25 pm
Thank you for the replay but no sorry thats not what I was trying,I dont think I have explained it very well.
What I want is to have a none for every item in the first column of the source data. Eg
Dept. Manager
Analysis None
Project. None
Planning None
Design None
Testing None
Analysis Noah
Project. Ava
Planning Noah
Design Sarah
Testing NoahBut I dont want to have to add a none to the source data every time I add a new dept
I tried to add a row of None to the preparation table [below the first transposed row and above the iferror formula] but the indirect formula wont pick up this data.
I have also tried adding a none in the validation box with the indirect formula and I have tried playing around to the formulas in the sheet. The most I can achieve is to add none to the end of every word in the drop down box and not as an extra drop down data.
I hope thats a clearer explanation.
Reply- Alexander Trifuntov [Ablebits Team] says:May 25, 2021 at 7:06 am
Hello!
The drop-down list and any Excel formula write the value only to the cell in which they are located. For two cells, you need to use VBA.Reply
- Jen says:May 19, 2021 at 6:16 pm
This has worked brilliantly.
Any chance you can add an extra data to the second drop down? So that the drop down list includes in the list none.
Example new dept, the list would say, Neal, Peter, None
I have tried adding a row to the preparation table but it wont let me reference that row.
Reply- Alexander Trifuntov [Ablebits Team] says:May 20, 2021 at 7:16 am
Hello!
You need to add a record to the Source Data table. In the first column - New Dept, in the second column - Neal, Peter, None.
I hope I answered your question. If you have any other questions, please dont hesitate to ask.Reply
- Zar says:May 6, 2021 at 12:58 am
Hello
I tried the same formula as mentioned in article. My problem is I cannot use # to get spill range. I can only get the top data for dependent list. If I put & "#" at the end, I always get "#REF!" error.
=INDIRECT[ADDRESS[3,COLUMN[A2]+MATCH[G15,Cost,0],4]&"#"]In my scenario, I have to use Cost [name for the title]. I cannot use $B$2# to get the array range.
How to use # to get the spill range please?
Reply - Sarah Clare says:April 28, 2021 at 12:19 am
Hi, this is a magical solution. Thank you.
I adjusted the formula to make the list expandable and it seems to work. See below. Is there a reason you didn't do it this way? Will is cause me trouble in the future? The sheet is dedicated to this data so there is no issue with any data going in that shouldn't be there.
=FILTER[$D:$D,$C:$C=H1]
Reply- Svetlana Cheusheva [Ablebits Team] says:April 28, 2021 at 9:13 am
Hi Sarah Clare,
Dragging this formula to the right through more columns than there are currently entries in the header row will produce a few columns with zero values in your preparation table. A zero will also appear in your primary dropdown. To prevent this from happening, you need to filter out all blanks as explained in the "How to make multiple drop-down list expandable" example.
Reply
- Monica says:April 27, 2021 at 5:16 pm
Thank you Svetlana. This was extremely helpful. I was struggling with this for many days. It is solved now.
One more help required. Your formula works only if preparation table is created in the same sheet where the drop downs is required. Can it be linked to some other sheet. [ The challenge I am facing is that the moment I insert a column, I have to again fix the formula in preparation table. If it is some other hidden protected tab, things would be easier.]Reply- Alexander Trifuntov [Ablebits Team] says:April 28, 2021 at 7:01 am
Hi,
You can use data from another sheet or a named range to create a dropdown list. Read more in this guide.
I hope my advice will help you solve your task.Reply - Svetlana Cheusheva [Ablebits Team] says:April 28, 2021 at 9:23 am
Hi Monica,
In "Step 3. Create a dependent drop-down list", there is a tip explaining how to adjust the formula for a preparation table on another sheet. Please check it out.
Reply
- Matt says:April 23, 2021 at 11:26 pm
Thank you Svetlana - this was a big help!
Reply - FoxRabbitHorse says:April 14, 2021 at 8:40 pm
This walk-through is great, the outcomes are magical. Thank you.
Reply - Duckduck says:March 25, 2021 at 11:21 pm
Hi
Is it possible to use a name with a "&" in a drop down list?
My first list has two such names: "FOOD & DRINKS" and "SHOES & CLOTHING".
I have found a way to deal with spaces for the 2nd drop downlist referring to the first: =INDIRECT[SUBSTITUTE[C274," ","_"]] and when i look in the name list, the categories with & are named FOOD___DRINKS [with 3 lowers in between] so was wondering if it could be solved the same or similar way.
=INDIRECT[SUBSTITUTE[C274," ","_","___"]] does not work.
Thanks!Reply - Bavu says:March 22, 2021 at 1:23 pm
For some odd reason, I am struggling to create the dependency drop-down list. Do you have youtube videos?
Reply- Svetlana Cheusheva [Ablebits Team] says:March 22, 2021 at 1:46 pm
Hi Bavu,
Nope. This solution is based on quite a complex formula for the dependent list, which you can copy from this tutorial, and then adjust references for your data set.
Reply
- x666 says:March 18, 2021 at 10:33 am
Hi,
excellent solution.... is there a way to get an advice [or a conditional formatting] in case someone changes the Dept after having filled the Manager? I mean for example in [Expandable drop down [table]] if someone changes [Dept.] from "Planning" to "Design", to see that "Noah" is no longer valid.Thanks
MarcoReply - Kaya says:March 17, 2021 at 12:48 pm
Hi, I can't for the life of me figure out how to replicate this for my purposes.
I have 4 columns of data that are dependent on one another. The values in column 4 is dependent on the value chosen from column 3, which is dependent on the value chosen from column 2, which is dependent on the value chosen from column 1. Would anyone be able to take a look at my file and let me know how I make this formula work for unique drop down dependent lists?
//docs.google.com/spreadsheets/d/10ZSDrPBm0tSeL20vZVF8odPht79A5M3KCk_uhYeISP0/edit?usp=sharing
Reply- Natalia Sharashova [Ablebits Team] says:March 18, 2021 at 7:21 am
Hi Kaya,
The file is closed for editing [we can only view the sheet]. Please share its editable copy with us [] so we could help you out. Thanks.
Reply - Ben says:August 18, 2021 at 8:30 pm
Hi,
Did yall figure out a solution to this? I am trying to do something similar.
Thanks!
Reply
- Kathy says:March 3, 2021 at 9:22 pm
Do you have any videos that show these steps?
Reply - Michael Rifer says:February 11, 2021 at 2:08 am
Hi, is there a way to make this approach work for a 3rd cascading picklist as well?
Reply- Alexander Trifuntov [Ablebits Team] says:February 11, 2021 at 8:24 am
Hello!
You can learn more about dependent drop-down list in this article: How to create multiple dependent drop down list in Excel.
It contains answers to your question.
I hope I answered your question. If something is still unclear, please feel free to ask.Reply- Mark Seely says:March 6, 2021 at 1:27 am
Hi Alexander,
The article you reference is one that shows how to do multiple dependant drop downs for one row, and this article shows you how to create one dependant drop down for multiple rows. Now, how do create multiple dependant drop downs for multiple rows?
My use case is a training log that will have hundreds of specific training courses to choose from so I want to have three pick lists to narrow down the potential courses for each entry [row]: 1] Course Category [Filter/UNIQUE], 2] Course Subject [dependant on course category], and 3] Course Title [dependant on course subject]. How do I do that?
Reply- David says:June 25, 2021 at 8:24 pm
I am trying to find the same thing as Mark above: combining both strategies into creating multiple dependant drop downs for multiple rows. In other words, replicate the functionality in the article Alexander referenced, but for multiple rows.
Reply
- Torkild Smith says:February 1, 2021 at 1:00 pm
Hi,
I am using another sheet for the source data but it comes up with an error for the second drop-down list in the data validation.
I think it may be the reference to the cell that is before the column in the prep tableThis one =INDIRECT[CHAR[CODE["E"]...
Is there a way to reference the other sheet in this setup?The full formula is: =INDIRECT[CHAR[CODE["E"]+MATCH[E2,sourcedata!$F$2#,0]]&"3#"]
Kind Regards
TorkildReply- Alexander Trifuntov [Ablebits Team] says:February 1, 2021 at 1:50 pm
Hello!
Unfortunately, without seeing your data it is difficult to give you any advice. Could you please describe your task in more detail and send us a small sample workbook with the source data and expected result to ? Please shorten your tables to 10-20 rows/columns and include the link to your blog comment.We'll look into your task and try to help.
Reply- Torkild Smith says:February 1, 2021 at 2:12 pm
Thanks, Alexander,
Just sent the email with attachments now.Kind Regards
TorkildReply - Torkild Smith says:February 1, 2021 at 5:03 pm
Thanks for the quick response and resolution to my problem.
5 Stars rating all round! *****Kind Regards
TorkildReply- Alexander Trifuntov [Ablebits Team] says:February 2, 2021 at 6:54 am
You are most welcome, Torkild! I was glad to help :]
Reply
- Paul Ryan says:August 24, 2021 at 4:44 am
For anyone else with this issue, simply change the source sheet name to Sheet1 and then copy the formula provided.
Once the formula is working correctly, you can change the name of the sheet back to whatever you please and the formula will be updated correctly.
Reply
- Jonathan says:January 22, 2021 at 9:07 pm
Thanks for this tutorial. It is incredible.
One question...
When inserting this formula into my DATA VALIDATION for a dependent dropdown list:INDIRECT[CHAR[CODE["col_letter"] + MATCH[dropdown_cell, dropdown_spill_range, 0]] & "row_num#"]
...I realize that it does not work ["results in an error] if the "col_letter" I am using is multiple letters. [in my case, the column preceding my data I need for the drop down list is in Column "BI". But sense the CODE function only returns the code for the first value...it is not working.
Any way to make this work in a DOUBLE LETTER column?
Reply- Alexander Trifuntov [Ablebits Team] says:January 25, 2021 at 8:54 am
Hello!
Without seeing your data it is difficult to give you any advice.
If I understand your task correctly, the following formula should work for you:=INDIRECT[ADDRESS["row_num#",COLUMN[INDIRECT["col_letter"&"1"]] + MATCH[dropdown_cell, dropdown_spill_range, 0]]]
I hope this will help, otherwise please do not hesitate to contact me anytime.
Reply - Svetlana Cheusheva [Ablebits Team] says:February 3, 2021 at 12:20 pm
Hi Jonathan,
Thank you for your question! I totally forgot about multi-letter columns, my bad. We have updated the formula and it now works fine for two-letter and even three-letter columns. Please see the "How this formula works" section for the detailed explanation.
Reply
- PJ says:January 22, 2021 at 6:15 pm
Svetlana, I see you are very proficient in EXCEL. I have a complicated formula that I need help with and can not figure out how to create it. Would you be willing to assist?
In Cell C29 there is a percentage [25%] automatically calculated. If that percentage is less than 25% then a number needs to be added into cell C5 to make C29 reach the required percentage of 25%. I have tried everything possible and can not seem to figure this out. Would you be able to assist? Greatly appreciated.
Reply- Alexander Trifuntov [Ablebits Team] says:January 25, 2021 at 7:08 am
Hello!
What formula do you use to calculate the percentage in C29? If a number is already written in C5, then you can change it either manually or using a VBA macro. Give an example of the source data and the expected result.
Itll help me understand it better and find a solution for you.Reply