Copying Formulas

[Guest]

I'm copying a formula that gets its values from a different worksheet and the
values are not from a serielised range. How can I write it so that the values
automatically pick up which cells to refer to? i.e
if CELL E6 has formula:
IF(SUM('Sheet1'!E$6:G$6) > 0,SUM('Sheet1'!E$6:G$6),"")

CELL E7 should have :
=IF(SUM('Sheet1'!H$6:J$6) > 0,SUM('Sheet1'!H$6:J$6),"")

CELL E8 should have :
=IF(SUM('Sheet1'!K$6:M$6) > 0,SUM('Sheet1'!K$6:M$6),"")

so thats E:G, H:J, K:M etc

 

[David Biddulph]

As you've described it, you've got a circular reference in E6, but if you
put the formula in D6 (for example) you can use
=IF(SUM(OFFSET(Sheet1!E$6,0,3*(ROW()-6),1,3)) >
0,SUM(OFFSET(Sheet1!E$6,0,3*(ROW()-6),1,3)),"")
and copy that down.

 

[Guest]

This is a little difficult for me to explain, but I think you'll see in the
formulas below that the key to this is knowing 2 things:
#1 - the row that your first IF(SUM( formula is on (row 6 in your example
since you say that formula is in E6
#2 - the number of columns you need to move over on the source sheet with
each row you move down on the sheet with your formulas in it: 3 columns each
time - E to H, H to K

Your first formula remains the same, but for the formulas in E8 and E9, you
need to use this format for the
SUM() portions within the IF statements:
SUM(OFFSET('Sheet1'!E$6:G$6,0,(ROW()-ROW(E$6))*3))

So the formula in E7 on your sheet becomes
=IF(SUM(OFFSET('Sheet1'!E$6:G$6,0,(ROW()-ROW(E$6))*3)) >
0,SUM(OFFSET('Sheet1'!E$6:G$6,0,(ROW()-ROW(E$6))*3)),"")

and oddly enough, that's exactly what your formula in E8 on that sheet
should look like and any more like it in the rows immediately following.