May 02 2021 10:00 PM
I want to get a dropdown list based on the value of another cell.
I've already got a formula. However I can't manually enter the value, I've to select it via list only. Can it be sorted?
I'm attaching the file
@Sergei Baklan @Peter Bartholomew @Detlef Lewin @Riny_van_Eekelen
May 02 2021 11:12 PM
Solution@Nishkarsh31 The solution would be not to use LET. Don't really understand why, but it seems overly complicated to begin with. Just repeat the code for the "yo" variable. Then, the formula for the data validation will look like this:
=OFFSET('Exp List'!$A$2,0,XMATCH(D6,'Exp List'!$A$1:$H$1)-1,COUNTA(OFFSET('Exp List'!$A$2,0,XMATCH(D6,'Exp List'!$A$1:$H$1)-1,100)))
or, if you would use some named ranges for the categories and starting point it could become like this:
=OFFSET(start,0,XMATCH(D6,categories)-1,COUNTA(OFFSET(start,0,XMATCH(D6,categories)-1,100)))
Not very complex and not really necessary to use LET. and it will allow you to enter the sub-category by typing.
May 03 2021 04:38 AM
May 02 2021 11:12 PM
Solution@Nishkarsh31 The solution would be not to use LET. Don't really understand why, but it seems overly complicated to begin with. Just repeat the code for the "yo" variable. Then, the formula for the data validation will look like this:
=OFFSET('Exp List'!$A$2,0,XMATCH(D6,'Exp List'!$A$1:$H$1)-1,COUNTA(OFFSET('Exp List'!$A$2,0,XMATCH(D6,'Exp List'!$A$1:$H$1)-1,100)))
or, if you would use some named ranges for the categories and starting point it could become like this:
=OFFSET(start,0,XMATCH(D6,categories)-1,COUNTA(OFFSET(start,0,XMATCH(D6,categories)-1,100)))
Not very complex and not really necessary to use LET. and it will allow you to enter the sub-category by typing.