Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Target cell=range??
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 -- alexsmalley ------------------------------------------------------------------------ alexsmalley's Profile: http://www.excelforum.com/member.php...o&userid=27729 View this thread: http://www.excelforum.com/showthread...hreadid=473714 |
#2
|
|||
|
|||
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)<=V13 55)) "alexsmalley" wrote: 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 -- alexsmalley ------------------------------------------------------------------------ alexsmalley's Profile: http://www.excelforum.com/member.php...o&userid=27729 View this thread: http://www.excelforum.com/showthread...hreadid=473714 |
#3
|
|||
|
|||
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" 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 -- alexsmalley ------------------------------------------------------------------------ alexsmalley's Profile: http://www.excelforum.com/member.php...o&userid=27729 View this thread: http://www.excelforum.com/showthread...hreadid=473714 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Charting data points and show a target range on the same chart. | Charts and Charting in Excel | |||
Using VBA to create a new worksheet, and then target new worksheet | Excel Discussion (Misc queries) | |||
Setting traffic lights based on todays date against target dates | Excel Discussion (Misc queries) | |||
Splitting a Target Figure | Excel Worksheet Functions | |||
Target line | Charts and Charting in Excel |