problems defining a sub range with INDEX formula

Question

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".

Answer 1

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...

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

Hope this helps!