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

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

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