I am trying to make a dynamic dependent drop-down list without taking into consideration zeros or empty cells with the UNIQUE() function in Excel. However, whenever i write the UNIQUE() command in the data validation tab, Excel always returns:
"The Source currently evaluates to an error. Do you want to continue?"
Here is what i'm trying to do: Explanation
In cell C4, i chose my category (so gaming, productivity, school, etc.). Columns I and J are here in case in the future, I want to add more categories. Then, in cell D4, depending on the category selected on cell C4, it will return the products of that category. However, forget about cell D4 for this question, once I figure out the category problem, I think I can find a solution by myself!
I first wrote: =UNIQUE(F1:J1;TRUE;TRUE) in an empty cell to verify if the function works and it does. The cells I1 and J1 don't appear, exactly how i want it to be. Now I need that in a dropdown list and so this is what I did: List
However, like I said earlier, Excel returns an error... Does someone know why? I even tried naming the range F1 to J1 as "Category" and wrote =UNIQUE(Category;TRUE;TRUE) but it still comes up with an error...