Cell reference in formula as range

[tjfwestcoast]

I have a formula in my spreadsheet that looks like:

=IF(D11=0," ",IF(C11<D11,"Not Enough Hours",INDEX('PF Kintanas Pivot
Tables'!$A$403:$A$418,MATCH($D11,t(E1),-1))))

In cell E1 I have the following text: gtalbo
In the PF Kintanas Pivot Tables tab I have a named range with the name: gtalbo

When I try to use the above formula I get the #Value! error. If I
substitute the actual name of the range in the formula it works:

=IF(D11=0," ",IF(C11<D11,"Not Enough Hours",INDEX('PF Kintanas Pivot
Tables'!$A$403:$A$418,MATCH($D11,gtalbo,-1))))

Is there anyway to make the cell reference work?

PS. I noticed when I performed the F9 function on the t(e1) portion of the
formula I get the result of:

=IF(D11=0," ",IF(C11<D11,"Not Enough Hours",INDEX('PF Kintanas Pivot
Tables'!$A$403:$A$418,MATCH($D11,"gtalbo",-1))))

Can I strip the quotes from this and make it work?

 

[T. Valko]

Instead of using:

t(E1)

Use:

INDIRECT(E1)

However, this will not work if the named range is defined using functions
like OFFSET.

 

[Dave Peterson]

if that named range is a single column or row, try:

...,MATCH($D11,indirect(E1),-1)...

ps. I think it's a bad idea to return a single space. If you want to make the
cell look empty, just use:

=if(d11=0,"",...

 

[tjfwestcoast]

Sweet, I love that command. I was actually trying to do this another way
where a build the range dynamically based on matching the unique key(in this
case gtalbo) to the start of the column I am matching on. The number of rows
is static(for now) so I was able to adapt the formula to eliminate the need
for a named range. Here is the resulting formula:

=IF(D11=0," ",IF(C11<D11,"Not Enough Hours",INDEX('PF Kintanas Pivot
Tables'!$A$403:$A$418,MATCH($D11,INDIRECT(CONCATENATE(ADDRESS(ROW('PF
Kintanas Pivot Tables'!$403:$403),MATCH($B11,'PF Kintanas Pivot
Tables'!$A$402:$BH$402),1,TRUE,"PF Kintanas Pivot
Tables"),":",ADDRESS(ROW('PF Kintanas Pivot Tables'!$418:$418),MATCH($B11,'PF
Kintanas Pivot Tables'!$A$402:$BH$402),1))),-1))))

Yes this may be ugly, but it works and I can eliminate some of the
hardcoding I was doing in the formula. The only issue I have now is if for
some reason the number of rows I am matching against grows past row 418.

Thanks again for the info

 

[T. Valko]

this may be ugly

but it works

And that's the most important thing!

Thanks for the feedback!