Forum Discussion
Dependent Drop Downs across multiple rows in a spreadsheet
Hello. I've been trying to create a spreadsheet that has a dependent dropdown column. However, all the guides and advice I've seen so far have been functional only when you only need a single cell or a small number of cells to have this drop-down. I'd like this function for a large spreadsheet that I will be adding to frequently.
My first thought as a workaround is - is there a way to have data validation read an adjacent cell? The issue I seem to be having is that while I can set Data Validation to =INDIRECT(A2) - it applies that to every cell in the column, so it will only search A2. Is there a a way to have data validation go =INDIRECT("CellAdjacentToThisOne")? I would have thought there's a formula for identifying whatever is in an adjacent cell, but I can't figure out what to look for sorry.
Failing that, if there's a workaround for getting dependent dropdowns to function through large spreadsheets, I'd love to hear it. I only have one layer of depdency - the first selection will draw from a wide range of options, but that will be the depth of it.
I'm wondering if part of the issue might be that I'm in Office 365 - some of the tutorials use the Name Manager, but it seems to function or maybe it just looks different in the desktop version of Excel, so maybe it has some feature I don't have access too?
Any advice would be appreciated.
If you select, for example, B2:B1000 and create data validation of type List with source =INDIRECT(A2), Excel will automatically adjust this: in B3 it will use =INDIRECT(B3), in B4 it will use =INDIRECT(B4), etc.
- Jambo97Copper ContributorTried to recreate that formula, but just getting an error. Shame since it seemed promising.
- peiyezhuBronze Contributor
- Jambo97Copper ContributorSo this is the closest solution I can find that actually works, using XLOOKUP: https://www.youtube.com/watch?v=fjn4vlWwpCo
However, when editing the Data validation version of XLOOKUP, it won't let me remove the dollar sign to allow it to be dragged down and function properly. Really annoying since this is so close.