Excel 2016 - DataValidation List with INDIRECT error with Named Formula

[natron8]

Thank you for reading through and helping!

I am using a Data Validation dropdown list with the INDIRECT function that pulls inputs from two cells and returns the list from one of eight named ranges (AllYellow, AllOrange, AllPurple, AllBlue, DefaultYellow, etc.). It works fine. The named ranges have a lot of blank cells in them and I found a way to filter the named range to not include blank cells that look like this:

=Sheet1!$C$2:INDEX(Sheet1!$C$2:$C$1000,SUMPRODUCT(--(Sheet1!$C$2:$C$1000<>"")))

When I enter this into my named range instead of =Sheet1!$C$2:$C$1000, the Value Column shows {,,,} instead of the data, BUT it does show up in a dropdown list when I set the source box equal to =AllYellow.

At this point, I have AllYellow mapped to the above formula and all other named ranges mapped to a fixed range. If my INDIRECT function shows AllYellow, I can't select anything from the dropdown list. If it shows any other range, there is no issue (aside from the blanks)

I also tried this with a mess of nested IF statements in the Data Validation Source box and it told me that "A named range you specified cannot be found", so no luck there.

Is there a solution to this or another path I should be going down to get the desired results?

[JeteMc]

Hello natron8 and Welcome to Excel Forum.
Perhaps the following will help.
1. Select cells C1:C4
2. Press the Ctrl key and select cells D1:D3
3. Press the Ctrl key are select cells E1:E3
4. Continue like this selecting only those cells containing a value through cells J1:J3
5. Press the Ctrl + Shift + F3 keys
6. From the Create Names from Selection dialog box make sure that Top row is checked.
7. Press OK.(You may be asked if you are sure you want to replace the current definition > select Yes)
8. If you need to add to or subtract from one of the lists simply select the cells in that column and repeat steps 5:7.
I feel that in this instance the above method is easier than writing complex formulas.
Let us know if you have any questions.