Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Peter
 
Posts: n/a
Default 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   Report Post  
N Harkawat
 
Posts: n/a
Default

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   Report Post  
Peter
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
cell reference in a formula is called ??????????? Excel Discussion (Misc queries) 2 April 22nd 23 09:02 AM
Reference to One Cell stays the same in Different Formula ISMEGORDO Excel Discussion (Misc queries) 10 June 16th 05 11:13 PM
Is it Possible in a formula to have a formula for cell reference? RogerBarker Excel Worksheet Functions 3 February 16th 05 10:00 PM
copying a formula, the reference adjusts, but the result does not chezoo Excel Discussion (Misc queries) 1 January 12th 05 02:51 AM
Using a formula in a Hyperlink Cell reference sslabbe Excel Discussion (Misc queries) 4 December 10th 04 01:30 AM


All times are GMT +1. The time now is 02:24 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"