Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum Formula with variable range | Excel Discussion (Misc queries) | |||
Formula based on variable range | Excel Worksheet Functions | |||
Excel Named Formula Weakly Interacts with a Variable Range on the Worksheet - Re-Visit | Excel Discussion (Misc queries) | |||
Variable Determines Number of Cell in Formula Range | Excel Worksheet Functions | |||
How to use a variable for a range | Excel Worksheet Functions |