Index a Named Range

[her.rockstar]

Hello, I have a worksheet (Variables) in my workbook with 73 dynamic named ranges.
On my Entry sheet, I need to find a way to index a named range of my choosing.

For example,
I will choose a named range from a drop-down

VARarea

Then below, I need to list the 10 (or however many) items resulting from that named range

I understand I can go
=index(VARarea,1)
which will bring me the first result, and then I could use 2 the line below, etc., but I need to be able to dynamically change the VARarea to something else. Using a cell reference doesn't work though. Seems like I need INDIRECT?

[PFDave]

If they are all named ranges and your first dropdown has them listed as they are named then you just use =INDIRECT("A1") inside the list source or whatever the location of the first dropdown box is in

[her.rockstar]

Hey PFDave, thanks for the response.

Say I've chosen VARarea in A1, if in A2 I use =INDIRECT("A1"), the result is VARarea.

I need it to bring me the first item in the VARarea list

[PFDave]

Ah so as opposed to wanting it to just define the next drop down box you want to know what options could be available and have those listed ?

[her.rockstar]

Yes exactly. Another example is the dynamic range VARtime.

If I select VARtime from my dropdown list in A1, I want A2:A50 to show this:

12:00 AM
12:30 AM
1:00 AM
1:30 AM
2:00 AM
2:30 AM
3:00 AM
3:30 AM
4:00 AM
4:30 AM
5:00 AM
5:30 AM
6:00 AM
6:30 AM
7:00 AM
7:30 AM
8:00 AM
8:30 AM
9:00 AM
9:30 AM
10:00 AM
10:30 AM
11:00 AM
11:30 AM
12:00 PM
12:30 PM
1:00 PM
1:30 PM
2:00 PM
2:30 PM
3:00 PM
3:30 PM
4:00 PM
4:30 PM
5:00 PM
5:30 PM
6:00 PM
6:30 PM
7:00 PM
7:30 PM
8:00 PM
8:30 PM
9:00 PM
9:30 PM
10:00 PM
10:30 PM
11:00 PM
11:30 PM
Not Listed

[PFDave]

ok so you could use =INDEX(INDIRECT($A$1),ROW()-1) and drag down, but as you have varying depths of range I'm not sure formula is the best method, I would be going for a vba solution

[her.rockstar]

I'm afraid it's coming back #REF!

[PFDave]

I'm afraid it's coming back #REF!

Fine in attached file,

Please can you upload a sample of your workbook so I can check the names match?

[her.rockstar]

Thank you for sending your sample file. I have a feeling the problem is coming from my Dynamic Named Range

VARtime
=OFFSET(Variables!$B$2,0,0,COUNTA(Variables!$B:$B)-1,1)

Suppose this could be the problem?

[romperstomper]

INDIRECT doesn't work with dynamic ranges. You could use a table though.

[her.rockstar]

Ah, that doesn't sound like a bad idea. Would that mean I won't need any named ranges?

[PFDave]

Ah, that doesn't sound like a bad idea. Would that mean I won't need any named ranges?

When you format as a data range they are essentially already named ranges for each column