Prev: Excel Column Renumbering Question
Next: formula for CF
From: Gabe on 12 May 2010 12:45
I'm stuck on this one, hopefullly someone can help. I have the following
formula:

=SUMPRODUCT((B2>=$B$5)*(B2<=$B$6))

Well "B2" in this formula is supposed to a variable range, but it wont
calculate right. So I created a bunch of different range names on sheet2
(i.e., B1:B100, A3:A7, etc...), and in B2 of sheet1 I created a combobox with
the validation tool for the user to basically choose the ranges I created. So
if they choose let's say the B1:B100 range in B2, how will that work in the
above formula?

Thanks,
~Gabe

From: Chip Pearson on 12 May 2010 13:29
Use the INDIRECT function. E.g.,

=SUMPRODUCT((INDIRECT(A1)>$B$5)*(INDIRECT(A1)<=$B$6))

The INDIRECT function will take the content of A1 as a reference. So,
if, for example, A1 contains the text 'K1:K10' Excel will calculate
the formula as if it were written

=SUMPRODUCT((K1:K10>$B$5)*(K1:K10<=$B$6))

The INDIRECT function can take any text string, built up in any manner
you desire and change it to an actual reference that can be used in a
formula. INDIRECTs can be nested as needed, allowing you to have a
chain of formulas that determine the final reference.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com




On Wed, 12 May 2010 09:45:01 -0700, Gabe
<Gabe(a)discussions.microsoft.com> wrote:

>I'm stuck on this one, hopefullly someone can help. I have the following
>formula:
>
>=SUMPRODUCT((B2>=$B$5)*(B2<=$B$6))
>
>Well "B2" in this formula is supposed to a variable range, but it wont
>calculate right. So I created a bunch of different range names on sheet2
>(i.e., B1:B100, A3:A7, etc...), and in B2 of sheet1 I created a combobox with
>the validation tool for the user to basically choose the ranges I created. So
>if they choose let's say the B1:B100 range in B2, how will that work in the
>above formula?
>
>Thanks,
>~Gabe
From: Gabe on 12 May 2010 13:42
Wait nevermind I think I got it, how about:

=SUMPRODUCT((INDIRECT(B2)>=$B$5)*(INDIRECT(B2)<=$B$6))

"Gabe" wrote:

> I'm stuck on this one, hopefullly someone can help. I have the following
> formula:
>
> =SUMPRODUCT((B2>=$B$5)*(B2<=$B$6))
>
> Well "B2" in this formula is supposed to a variable range, but it wont
> calculate right. So I created a bunch of different range names on sheet2
> (i.e., B1:B100, A3:A7, etc...), and in B2 of sheet1 I created a combobox with
> the validation tool for the user to basically choose the ranges I created. So
> if they choose let's say the B1:B100 range in B2, how will that work in the
> above formula?
>
> Thanks,
> ~Gabe
>
 | 
Pages: 1
Prev: Excel Column Renumbering Question
Next: formula for CF