If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Variable Range in Formula
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 |
#2
|
|||
|
|||
Variable Range in Formula
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 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 |
#3
|
|||
|
|||
Variable Range in Formula
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 |
Thread Tools | |
Display Modes | |
|
|