problems defining a sub range with INDEX formula

My understanding is that you can define a range using index. example I can set a defined name of MyList to

 =index(A:A,3,1):index(A:A,5,1)

This would be the equivalent of saying A3:A5. I can then turn around and use index(MyList,1,1) and I would see the contents of A3. All this works for me.

So I was trying to define a range of sheet names. I used defined name sheetnames as:

 =TRANSPOSE(GET.WORKBOOK(1,Structural!$J$3)&T(NOW()))

(I used transpose to get the list vertical)

when I use:

 =INDEX(Sheetnames,3,1)
 =INDEX(Sheetnames,6,1)

I get the name of my 3rd or 6th sheet in my workbook respectively. So that part is working. However when I try to define a range like I did for MyList using the following I get #value

 =INDEX(INDEX(Sheetnames,3,1):INDEX(Sheetnames,6,1),1,1)

QUESTION:

Why is it not working?

As a test to get first sheetname I have also tried:

 =OFFSET(Sheetnames,1,1,1,1)

This also gave the same error.

What I am ultimately trying to do is generate a pull down list through data validation of all sheet names except the sheets named "Index" and "Master".

As per Excel's help file on INDEX...

Reference form

Description

Returns the reference of the cell at the intersection of a particular row and column. If the reference is made up of nonadjacent selections, you can pick the selection to look in.

Syntax

INDEX(reference, row_num, [column_num], [area_num])

The INDEX function syntax has the following arguments.

Reference Required. A reference to one or more cell ranges

etc...

Therefore, in order to return a reference, you would need to reference a range of cells. SheetNames, however, doesn't refer to a range of cells. It refers to GET.WORKBOOK, which returns an array of values. In this case, it returns an array of sheet names.

So with the following formula...

=INDEX(INDEX(Sheetnames,3,1):INDEX(Sheetnames,6,1),1,1)

...it gets evaluated as follows (assuming the workbook is called Book1.xlsx and you have Sheet1, Sheet2, Sheet3, etc)...

--> some preliminary evaluations <---

=INDEX("[Book1.xlsx]Sheet3":"[Book1.xlsx]Sheet6",1,1)

=INDEX(#VALUE!,1,1)

=#VALUE!

You can evaluate the formula for yourself by selecting the cell containing the formula, and stepping through it using the Evaluate Formula button on the Ribbon (Formulas tab > Formula Auditing group).

You can also confirm that INDEX doesn't return a reference in this case by using the ISREF function. The following formula should return FALSE...