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 in sheet2 has formula:
IF(SUM('Sheet1'!E$6:G$6) 0,SUM('Sheet1'!E$6:G$6),"")
CELL F6 should have :
=IF(SUM('Sheet1'!H$6:J$6) 0,SUM('Sheet1'!H$6:J$6),"")
CELL G6 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


Thus the columns must reflect the change after every 3rd column but I'm
referencing the same row.
--
Wether you think you can or you think you cant , you are right ... anon

See your earlier post.
--
David Biddulph

"Mtabaruka" wrote in message
...
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 in sheet2 has formula:
IF(SUM('Sheet1'!E$6:G$6) 0,SUM('Sheet1'!E$6:G$6),"")
CELL F6 should have :
=IF(SUM('Sheet1'!H$6:J$6) 0,SUM('Sheet1'!H$6:J$6),"")
CELL G6 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


Thus the columns must reflect the change after every 3rd column but I'm
referencing the same row.
--
Wether you think you can or you think you cant , you are right ... anon

My earlier post was incorrectly stated. I implied that the rows also change
but that is not the case all I want to change are the columns. I've tried to
alter the formula without success.
--
Wether you think you can or you think you cant , you are right ... anon


"David Biddulph" wrote:

See your earlier post.
--
David Biddulph

"Mtabaruka" wrote in message
...
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 in sheet2 has formula:
IF(SUM('Sheet1'!E$6:G$6) 0,SUM('Sheet1'!E$6:G$6),"")
CELL F6 should have :
=IF(SUM('Sheet1'!H$6:J$6) 0,SUM('Sheet1'!H$6:J$6),"")
CELL G6 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


Thus the columns must reflect the change after every 3rd column but I'm
referencing the same row.
--
Wether you think you can or you think you cant , you are right ... anon

You've still got a circular reference in E6, and will need to sort that out,
but the answer is still to use OFFSET, but this time you'll need to use
COLUMN() in the formula, not ROW().
--
David Biddulph

"Mtabaruka" wrote in message
...
My earlier post was incorrectly stated. I implied that the rows also
change
but that is not the case all I want to change are the columns. I've tried
to
alter the formula without success.
--
Wether you think you can or you think you cant , you are right ... anon


"David Biddulph" wrote:

See your earlier post.
--
David Biddulph

"Mtabaruka" wrote in message
...
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 in sheet2 has formula:
IF(SUM('Sheet1'!E$6:G$6) 0,SUM('Sheet1'!E$6:G$6),"")
CELL F6 should have :
=IF(SUM('Sheet1'!H$6:J$6) 0,SUM('Sheet1'!H$6:J$6),"")
CELL G6 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


Thus the columns must reflect the change after every 3rd column but I'm
referencing the same row.
--
Wether you think you can or you think you cant , you are right ... anon