Copying a column list from one sheet to another and repeating?
I have tested the formula which I have used as follows...
=OFFSET('Sheet 1'!$B$10,((MOD(COLUMN(),(COUNTA('Sheet
1'!$B$11:$B$20)-(COUNTBLANK('Sheet 1'!$B$11:$B$20))))=0)*(COUNTA('Sheet
1'!$B$11:$B$20)-(COUNTBLANK('Sheet
1'!$B$11:$B$20)))+(MOD(COLUMN(),(COUNTA('Sheet
1'!$B$11:$B$20)-(COUNTBLANK('Sheet 1'!$B$11:$B$20)))))),0)
As you can see from the formula, the list heading on sheet 1 is at cell B10
and the first list item is at cell B11. However, when I paste the above
formula into cell B15 on sheet 2 the first item displayed from the list on
sheet 1 is cell B12 and not B11.
Other than that the formula seems to work and it does repeat the list
correctly across the full row, it's just the first item that isn't working.
Is there a way to solve this?
Thanks
"klswvu" wrote:
=OFFSET(Sheet1!$A$2,((MOD(COLUMN(),(COUNTA(Sheet1! $A$3:$A$10)-(COUNTBLANK(Sheet1!$A$3:$A$10))))=0)*(COUNTA(Sheet 1!$A$3:$A$10)-(COUNTBLANK(Sheet1!$A$3:$A$10)))+(MOD(COLUMN(),(CO UNTA(Sheet1!$A$3:$A$10)-(COUNTBLANK(Sheet1!$A$3:$A$10)))))),0)
$A$2 is the heading and anchor for the offset
$A$3:$A$10 is the range where the text is pulled from
|