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

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

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

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

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

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

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