It's not something I have tried to do in the past, but I have played about with it a bit. I thought this might work in F1:

=INDEX(INDIRECT($E$1),ROWS($1:1),1)

with Range1 defined as:

=Sheet1!$A$1:INDEX(Sheet1!$D:$D,COUNT(Sheet1!$A:$A))

but I also got the #Ref! error, although this works:

=INDEX(Range1,ROWS($1:1),1)

and you can copy it down. I also tried with a definition for Range1 of:

=Sheet1!$A$1:$D$20

and then the first formula, i.e.:

=INDEX(INDIRECT($E$1),ROWS($1:1),1)

also worked when copied down.

Like Glenn, I'm not sure what you are trying to do, but I must conclude, as you have, that you can't use INDIRECT on a dynamic named range.

Hope this helps.

Pete