The overall goal of my project is to allow the user to add the "CATEGORY" (column B) and the "SUB-CATEGORY" (column C) to the "DESCRIPTION" (column A) for each row with as little room for error as possible. Therefore, by limiting their options in column "C" based on their input into column "B", I am getting closer to making it idiotproof! :-)
So right now I have this formula in EACH ROW of column "D":
=IFNA(TRANSPOSE(FILTER('Category Setup'!$C$3:$C$42,'Category Setup'!$B$3:$B$42='FULL LIST'!$B2)),"")
...and it works ok. But I'll be ending up with MANY of these pages, so cleaning it up now would be preferred.
I'm trying, instead, to use an Array at the top of column "D".
Of course I tried adding "Arrayformula" to the start of the formula...
=ARRAYFORMULA(IFNA(TRANSPOSE(FILTER('Category Setup'!$C$3:$C$42,'Category Setup'!$B$3:$B$42='FULL LIST'!$B2)),""))
... but it isn't working (I THINK it's because of the "Transpose" option).
Again, the purpose of the formula is to qualify the results for the 2nd dropdown based off of the results of the 1st one.
As always, THANKS!
dgDG
You may try this in Cell_D1:
=ifna(vstack("Data Validation",
map(B2:B,lambda(Σ,if(Σ="",,torow(filter('Category Setup'!C3:C42,'Category Setup'!B3:B42=Σ)))))))