Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Vary variables in a formula via reference to another cell
I have a formula =SUMPRODUCT(--(b1:b10=a1),c1:c10) for a report I run
January. In Feb, I want to change c1.c10 to d1.d10 and in March to e1.e10 etc. Ideal I'd like to name range c1.c10 "Jan" and d1.d10 "feb" etc and then enter the month range name in cell x1 (eg: Jan) and have the formula pick it up eg: =SUMPRODUCT(--(b1:b10=a1),+X1). But I can't get it to work! Any ideas....? TVM. -- Peter London, UK |
#2
|
|||
|
|||
mask the cell x1 with indirect function as follows:
=SUMPRODUCT(--(b1:b10=a1),(indirect(X1))) "Peter" wrote in message ... I have a formula =SUMPRODUCT(--(b1:b10=a1),c1:c10) for a report I run January. In Feb, I want to change c1.c10 to d1.d10 and in March to e1.e10 etc. Ideal I'd like to name range c1.c10 "Jan" and d1.d10 "feb" etc and then enter the month range name in cell x1 (eg: Jan) and have the formula pick it up eg: =SUMPRODUCT(--(b1:b10=a1),+X1). But I can't get it to work! Any ideas....? TVM. -- Peter London, UK |
#3
|
|||
|
|||
Thanks Mr Harkawat. You're a star!
-- Peter London, UK "N Harkawat" wrote: mask the cell x1 with indirect function as follows: =SUMPRODUCT(--(b1:b10=a1),(indirect(X1))) "Peter" wrote in message ... I have a formula =SUMPRODUCT(--(b1:b10=a1),c1:c10) for a report I run January. In Feb, I want to change c1.c10 to d1.d10 and in March to e1.e10 etc. Ideal I'd like to name range c1.c10 "Jan" and d1.d10 "feb" etc and then enter the month range name in cell x1 (eg: Jan) and have the formula pick it up eg: =SUMPRODUCT(--(b1:b10=a1),+X1). But I can't get it to work! Any ideas....? TVM. -- Peter London, UK |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cell reference in a formula is called | Excel Discussion (Misc queries) | |||
Reference to One Cell stays the same in Different Formula | Excel Discussion (Misc queries) | |||
Is it Possible in a formula to have a formula for cell reference? | Excel Worksheet Functions | |||
copying a formula, the reference adjusts, but the result does not | Excel Discussion (Misc queries) | |||
Using a formula in a Hyperlink Cell reference | Excel Discussion (Misc queries) |