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.

​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.  Read More

​