Creating a Dependent Drop-Down List where the VALUES (not just the list) come from an INDIRECT Function

The software our company uses utilizes SpreadsheetGear to build dashboards for Estimating. The data from our estimating software is fed into a sheet into this dashboard, and I build my formulas to read things from that sheet.
The limitation with SpreadsheetGear is that it doesn't have any of the fancy new array formulas like UNIQUE or SORT.

What I am trying to do, is have a drop-down list which can be read by another cell, which calls up one of four lists using Names. This is a standard dependent drop-down list, and is achieved using Data Validation and in the Source, referencing a cell that can only output named ranges.

The issue comes when instead of using Named Ranges (where the names are just references to a range), I want to use an indirect function to call up a shortened version of this range.

For example
I can pick from a List from a List in cell A1: List1,List2,List3.
Then in cell B1 I want to generate a dropdown list based on what I picked in A1.

List1 is in C1:C100, but only 8 values are filled. So I have another cell which uses some formulas to figure out the last non-blank cell, and in D1 I generate the text value "$C$1:$C$9".
If I just use =$C$1:$C$100 in the 'Refers to' as a Named Range, excel allows me to use this dependent drop-down list. But my dependent drop-down list is 100 values long.
If instead of a named range I use "=INDIRECT(D1)" excel won't allow me to use the drop-down list.

The goal is I only want my dropdown list to be 8 values if only 8 values are filled, but expand more as more values are added through the estimating software.

It seems like using INDIRECT to call a text "range reference" in a Name, and then using another INDIRECT in the data validation list source to reference said Name is too much. But I want to open this problem up to the floor to see if anyone can give me a hand on it.

Alternatively, if there is a way to filter out the blanks (I've tried OFFSET within the Name along with the named range, without much success.)

I've tried putting INDIRECT to both the Name and the Data Validation, INDIRECT to the first but not the second, INDIRECT to the second but not the first.

Further Edit

I'll limit this to 2 ranges to keep it simple, but extending it to the 4 requested (or more) would be trivial.

Define the dynamic ranges (List1, List2) as described in my original post below. Here I've defined List1 as C1:C100 and List2 as D1:D100.

In the Data Validation dialog for A5, use this formula (the selection in A2 tells Excel which Indirect statement to use for the drop-down in A5):

=CHOOSE($A$2, INDIRECT(List1), INDIRECT(List2))

Select the number of the validation list to use (select 1 for List1, etc.) in A2. That list is then used for validation in A5. The picture shows:

• Left: choose the list in A2
• Center: validation based on List1 in A5
• Right: validation based on List2 in A5

If you need the actual list names in A2 instead of their number, that's possible but it's a further complication. Let's get this working as desired first.

Original (mostly)

You can do this with dynamic ranges. It's a bit confusing, but bear with me.

First, define the range. If List1 is in C1:C100 on Sheet1, insert this formula in the New Name dialog:

"Sheet1!$c$1:$c$" & COUNTA(Sheet1!c1:c100)

The COUNTA() determines the last row in the range (the entries must be in contiguous rows or this will fail).