Been beating my head against a wall for a few days trying to figure out how to get a dependent drop-down list to function in the Desktop Office 365 version of Excel.

I’ve finally found something that’s very close to working, using an XLOOKUP function that checks the adjacent cell, checks for a match, then outputs everything under that match. That works really well.

However my final hurdle, and the one that seems to be a recurring problem across all the methods I’ve tried, is getting the data validation to work when dragged down to other cells.

For my first column, that drags down perfectly well – since its just choosing from a static range. However, the second column doesn’t work like that. It only ever reads one cell, the cell it was entered with.

When I enter Data validation and select a cell, it has dollar signs next to both figures. I was reading that the dollar sign sets a value as absolute, so doesn’t shift when dragged down. However, Office 365 won’t let me remove the dollar signs from the validation.

How I’ve got it set-up in my Data Validation at the moment (that works for one row) is:

=XLOOKUP($A$3,Class,Subclass)

(Class being the static dropdown list, Subclass being the dependent dropdown list).

I can’t remove either of the $ signs without it refusing to accept it. Is there a work around to this, or another way to tell a cell “Hey, you’re looking for the cell to your left”.

As an alternative “Me thinking of things that probably won’t work” solution – where I have the XLOOKUP set up in the sheet, the data is descening vertically. I can drag it across horizontally, but that won’t work for how I have the table-setup. I was wondering if it was possible to have the XLOOKUP present its data in the columns to the right of it, instead of below. Then I can at least just drag that formula downwards maybe and see if that works?

​Been beating my head against a wall for a few days trying to figure out how to get a dependent drop-down list to function in the Desktop Office 365 version of Excel. I’ve finally found something that’s very close to working, using an XLOOKUP function that checks the adjacent cell, checks for a match, then outputs everything under that match. That works really well. However my final hurdle, and the one that seems to be a recurring problem across all the methods I’ve tried, is getting the data validation to work when dragged down to other cells.For my first column, that drags down perfectly well – since its just choosing from a static range. However, the second column doesn’t work like that. It only ever reads one cell, the cell it was entered with. When I enter Data validation and select a cell, it has dollar signs next to both figures. I was reading that the dollar sign sets a value as absolute, so doesn’t shift when dragged down. However, Office 365 won’t let me remove the dollar signs from the validation. How I’ve got it set-up in my Data Validation at the moment (that works for one row) is: =XLOOKUP($A$3,Class,Subclass) (Class being the static dropdown list, Subclass being the dependent dropdown list). I can’t remove either of the $ signs without it refusing to accept it. Is there a work around to this, or another way to tell a cell “Hey, you’re looking for the cell to your left”. As an alternative “Me thinking of things that probably won’t work” solution – where I have the XLOOKUP set up in the sheet, the data is descening vertically. I can drag it across horizontally, but that won’t work for how I have the table-setup. I was wondering if it was possible to have the XLOOKUP present its data in the columns to the right of it, instead of below. Then I can at least just drag that formula downwards maybe and see if that works?                     Read More

​