Cell reference in formula as range

T

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

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

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

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:)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top