use range name in formula

[Guest]

I have been trying without any success to refer to a range name by using text
within a cell.

To set the scene......

There are three names ranges which are various lengths on one part of the
worksheet.

There is a cell on another part of the worksheet that contains text. This
text will be the same as one of the name of a named ranged.

I am trying to get index() to use the text as a range name, but it will only
use it as text.

This formaula works
=SUMIF(INDEX(Subway,,1),H8,INDEX(Subway,,5))

Subway is the range name, however, when I try to get index to use the text
within a cell it does not work.

=SUMIF(INDEX(J5,,1),H8,INDEX(J5,,5))

J5 contains the word Subway

The formula result is #ref

Looking at the result it appears that J5 just returns that cell, the first
index works as it calls for the first column, the second index fails because
it is calling the fifth colum, but J5 is only one column wide, hence the
ref#. All I want is for index to use J5 to pick up the range name to use.

Can anyone help?

 

[Bob Phillips]

=SUMIF(INDEX(INDIRECT(J5),,1),H8,INDEX(INDIRECT(J5),,5))

 

[JE McGimpsey]

One way:

=SUMIF(INDEX(INDIRECT(J5),,1),H8,INDEX(INDIRECT(J5),,5))

 

[Guest]

Thanks Guys, that did the trick.
Much appreciated