Copying Formulas - correctly stated
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 |
Copying Formulas - correctly stated
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 |
Copying Formulas - correctly stated
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 |
Copying Formulas - correctly stated
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 |
All times are GMT +1. The time now is 10:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com