Target cell=range??

A

alexsmalley

I am compiling a series of tables that calculate the occurences o
multiple data sets that satisfy specific conditions. I must apply thes
tables to ever changing source data (within the same worksheet). Th
formulas that exist within the table are absolute cell references eg

=SUMPRODUCT(($E$1355:$E$1978>=0)*($D$1355:$D$1978<=0.49)*($D$1355:$D$1978>=0)*($G$1355:$G$1978<=2.99)*($G$1355:$G$1978>=U1355)*($G$1355:$G$1978<=V1355))

is there a method by which the absolute cell reference could be entere
in another target cell such that

($E$1355:$E$1978>=0) becomes (A1>=0)

where A1 gives the range that must be considered, in this cas
$E$1355:$E$1978

????????

Many thanks

Ale
 
B

bj

try something like the offset() function
in A1 put $E$1355 in A2 put 623
if the relationships between the sections is always the same

=SUMPRODUCT((Offset($A$1,0,0,$A$2)>=0)*(Offset($A$1,0,-1,$A$2)<=0.49)*(Offset($A$1,0,-1,$A$2)>=0)*(Offset($A$1,0,2,$A$2)<=2.99)*(Offset($A$1,0,2,$A$2)>=U1355)*(Offset($A$1,0,2,$A$2)<=V1355))
 
R

RagDyeR

You can use Indirect() for the column and row references.

A1 = $E$1355:$E$1978

A2 = $D$1355:$D$1978

A3 = $G$1355:$G$1978

=SUMPRODUCT((INDIRECT(A1)>=0)*(INDIRECT(A2)<=0.49)*(INDIRECT(A2)>=0)*(INDIRE
CT(A3)<=2.99)*(INDIRECT(A3)>=U1355)*(INDIRECT(A3)<=V1355))


--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

"alexsmalley" <[email protected]>
wrote in message

I am compiling a series of tables that calculate the occurences of
multiple data sets that satisfy specific conditions. I must apply these
tables to ever changing source data (within the same worksheet). The
formulas that exist within the table are absolute cell references eg

=SUMPRODUCT(($E$1355:$E$1978>=0)*($D$1355:$D$1978<=0.49)*($D$1355:$D$1978>=0
)*($G$1355:$G$1978<=2.99)*($G$1355:$G$1978>=U1355)*($G$1355:$G$1978<=V1355))

is there a method by which the absolute cell reference could be entered
in another target cell such that

($E$1355:$E$1978>=0) becomes (A1>=0)

where A1 gives the range that must be considered, in this case
$E$1355:$E$1978

????????

Many thanks

Alex
 
Top