| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell reference in formula as range
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Cell reference in formula as range
Instead of using:
t(E1) Use: INDIRECT(E1) However, this will not work if the named range is defined using functions like OFFSET. -- Biff Microsoft Excel MVP "tjfwestcoast" wrote in message ... 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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Cell reference in formula as range
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(CONCATEN ATE(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" wrote: Instead of using: t(E1) Use: INDIRECT(E1) However, this will not work if the named range is defined using functions like OFFSET. -- Biff Microsoft Excel MVP "tjfwestcoast" wrote in message ... 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? . |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Cell reference in formula as range
this may be ugly
Yeah, it's certainly getting there! <g but it works And that's the most important thing! Thanks for the feedback! -- Biff Microsoft Excel MVP "tjfwestcoast" wrote in message ... 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(CONCATEN ATE(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" wrote: Instead of using: t(E1) Use: INDIRECT(E1) However, this will not work if the named range is defined using functions like OFFSET. -- Biff Microsoft Excel MVP "tjfwestcoast" wrote in message ... 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? . |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Cell reference in formula as range
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 wrote: 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? -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| in formula a range cell reference eg) $A1:$A20 that is static whenfilling | New Users to Excel | |||
| Variable Cell Range Reference | Excel Worksheet Functions | |||
| Cell reference in a range | Excel Worksheet Functions | |||
| using a dummy cell to reference a range into a formula | Excel Discussion (Misc queries) | |||
| Cell Reference with Range Name | Excel Worksheet Functions | |||
Hybrid Mode
