| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable named range in worksheet function
I'd like to use a named range of "FactorA" (which contains multiple cells),
etc, and I'm trying to do something like this =AVERAGE(IF(INDIRECT("Factor" &$B17)=$D17,IF($F$2:$F$9=E$16,$R$2:$R$9))) Where B17 contains the A. Can I do this? Thanks, Barb Reinhardt |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Variable named range in worksheet function
Yes. For that particular formula to work the range should be of equal size
to the other cell ranges or else you will probably get a N/A error -- Regards, Peo Sjoblom "Barb Reinhardt" wrote in message ... I'd like to use a named range of "FactorA" (which contains multiple cells), etc, and I'm trying to do something like this =AVERAGE(IF(INDIRECT("Factor" &$B17)=$D17,IF($F$2:$F$9=E$16,$R$2:$R$9))) Where B17 contains the A. Can I do this? Thanks, Barb Reinhardt |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Variable named range in worksheet function
I'm actually getting a REF error when I put the named range into the
Indirect. This formula works fine (in an adjacent cell) =AVERAGE(IF($F$2:$F$9=$D18,IF($F$2:$F$9=F$16,$R$2: $R$9))) And the range name is currently defined to be $F$2:$F$9. I still commit it with CTRL SHIFT ENTER. Thanks, Barb Reinhardt "Peo Sjoblom" wrote: Yes. For that particular formula to work the range should be of equal size to the other cell ranges or else you will probably get a N/A error -- Regards, Peo Sjoblom "Barb Reinhardt" wrote in message ... I'd like to use a named range of "FactorA" (which contains multiple cells), etc, and I'm trying to do something like this =AVERAGE(IF(INDIRECT("Factor" &$B17)=$D17,IF($F$2:$F$9=E$16,$R$2:$R$9))) Where B17 contains the A. Can I do this? Thanks, Barb Reinhardt |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Variable named range in worksheet function
FWIW, when I step through the calculation, I get REF on the INDIRECT("Factor"
& $B17) part. Thanks, Barb Reinhardt "Peo Sjoblom" wrote: Yes. For that particular formula to work the range should be of equal size to the other cell ranges or else you will probably get a N/A error -- Regards, Peo Sjoblom "Barb Reinhardt" wrote in message ... I'd like to use a named range of "FactorA" (which contains multiple cells), etc, and I'm trying to do something like this =AVERAGE(IF(INDIRECT("Factor" &$B17)=$D17,IF($F$2:$F$9=E$16,$R$2:$R$9))) Where B17 contains the A. Can I do this? Thanks, Barb Reinhardt |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Variable named range in worksheet function
How does your named range look, I assumed you used a straightforward one
like Sheet!!$A$2:$A$9 if you use offset to make it dynamic it won't work -- Regards, Peo Sjoblom "Barb Reinhardt" wrote in message ... FWIW, when I step through the calculation, I get REF on the INDIRECT("Factor" & $B17) part. Thanks, Barb Reinhardt "Peo Sjoblom" wrote: Yes. For that particular formula to work the range should be of equal size to the other cell ranges or else you will probably get a N/A error -- Regards, Peo Sjoblom "Barb Reinhardt" wrote in message ... I'd like to use a named range of "FactorA" (which contains multiple cells), etc, and I'm trying to do something like this =AVERAGE(IF(INDIRECT("Factor" &$B17)=$D17,IF($F$2:$F$9=E$16,$R$2:$R$9))) Where B17 contains the A. Can I do this? Thanks, Barb Reinhardt |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Variable named range in worksheet function
I used OFFSET. Is that my problem? I like OFFSET. Why won't it work that
way? Thanks, Barb Reinhardt "Peo Sjoblom" wrote: How does your named range look, I assumed you used a straightforward one like Sheet!!$A$2:$A$9 if you use offset to make it dynamic it won't work -- Regards, Peo Sjoblom "Barb Reinhardt" wrote in message ... FWIW, when I step through the calculation, I get REF on the INDIRECT("Factor" & $B17) part. Thanks, Barb Reinhardt "Peo Sjoblom" wrote: Yes. For that particular formula to work the range should be of equal size to the other cell ranges or else you will probably get a N/A error -- Regards, Peo Sjoblom "Barb Reinhardt" wrote in message ... I'd like to use a named range of "FactorA" (which contains multiple cells), etc, and I'm trying to do something like this =AVERAGE(IF(INDIRECT("Factor" &$B17)=$D17,IF($F$2:$F$9=E$16,$R$2:$R$9))) Where B17 contains the A. Can I do this? Thanks, Barb Reinhardt |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Variable named range in worksheet function
See this:
http://tinyurl.com/6lufyy -- Biff Microsoft Excel MVP "Barb Reinhardt" wrote in message ... I used OFFSET. Is that my problem? I like OFFSET. Why won't it work that way? Thanks, Barb Reinhardt "Peo Sjoblom" wrote: How does your named range look, I assumed you used a straightforward one like Sheet!!$A$2:$A$9 if you use offset to make it dynamic it won't work -- Regards, Peo Sjoblom "Barb Reinhardt" wrote in message ... FWIW, when I step through the calculation, I get REF on the INDIRECT("Factor" & $B17) part. Thanks, Barb Reinhardt "Peo Sjoblom" wrote: Yes. For that particular formula to work the range should be of equal size to the other cell ranges or else you will probably get a N/A error -- Regards, Peo Sjoblom "Barb Reinhardt" wrote in message ... I'd like to use a named range of "FactorA" (which contains multiple cells), etc, and I'm trying to do something like this =AVERAGE(IF(INDIRECT("Factor" &$B17)=$D17,IF($F$2:$F$9=E$16,$R$2:$R$9))) Where B17 contains the A. Can I do this? Thanks, Barb Reinhardt |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using named range to extend print area for variable number of columns | Excel Worksheet Functions | |||
| Do not specify worksheet in a named range | Excel Discussion (Misc queries) | |||
| Excel Named Formula Weakly Interacts with a Variable Range on the Worksheet - Re-Visit | Excel Discussion (Misc queries) | |||
| Getting a named range to appear in another worksheet | Excel Discussion (Misc queries) | |||
| Variable names for named range | Excel Discussion (Misc queries) | |||
Hybrid Mode
