If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Copying a column list from one sheet to another and repeating?
I tried this formula you suggested...
=OFFSET('Sheet1'!$B$11,((MOD(COLUMN(),(COUNTA('She et1'!$B$11:$B$100)))=0)*(COUNTA('Sheet1'!$B$11:$B$ 100)))+(MOD(COLUMN(),(COUNTA('Sheet1'!$B$11:$B$100 )))),0) But instead of copying from the start of the list at Sheet1 cell B11 it is starting at cell B13. Also, if I copy the first cell on sheet 2 using this formula at B10 across the entire row of 30 cells being used it doesn't repeat the current list of 6 items. There are 14 blank cells, then a cell containing a 0 then the list starts repeating but from B13 again. Any ideas why this is or what I am doing wrong? Thanks "klswvu" wrote: =OFFSET('Sheet 1'!$A$2,((MOD(COLUMN(),(COUNTA('Sheet1'!$A$2:$A$10 )))=0)*(COUNTA('Sheet1'!$A$2:$A$10)))+(MOD(COLUMN( ),(COUNTA('Sheet 1'!$A$2:$A$10)))),0) Explanation: OFFSET(reference, rows, columns, [height], [width]) returns the cells x rows and y columns from the reference Reference is an anchor point... top of your product range $A$2 Determine how many rows down from the reference anchor... ((MOD(COLUMN(),(COUNTA('Sheet1'!$A$2:$A$10)))=0)*( COUNTA('Sheet1'!$A$2:$A$10)))+(MOD(COLUMN(),(COUNT A('Sheet 1'!$A$2:$A$10)))) (MOD(COLUMN(),(COUNTA('Sheet1'!$A$2:$A$10)))=0) ... returns the remainder of the column number by the total count of the range and determine if it is zero (returns 0 or 1) *(COUNTA('Sheet1'!$A$2:$A$10)) ... multiple by the total count of the range +(MOD(COLUMN(),(COUNTA('Sheet 1'!$A$2:$A$10))) ... add the count of the range Assume the range has seven values... column 1 would result in (0*1)+1 = 1 so go down one row from the reference anchor column 2 would result in (0*2)+2 = 2 so go down one row from the reference anchor column 3 would result in (0*3)+3 = 3 so go down one row from the reference anchor column 4 would result in (0*4)+4 = 4 so go down one row from the reference anchor column 5 would result in (0*5)+5 = 5 so go down one row from the reference anchor column 6 would result in (0*6)+6 = 6 so go down one row from the reference anchor column 7 would result in (1*7)+0 = 7 so go down one row from the reference anchor column 8 would result in (0*1)+1 = 1 so go down one row from the reference anchor Determine how many columns across from the reference anchor... ,0 ... it is zero |
#12
|
|||
|
|||
Copying a column list from one sheet to another and repeating?
The reference should be 1 cell above your first value you want to return or
you need the -1 one at the end... this case reference the heading at $A$3 A 1 2 3 Heading 4 Value 1 5 Value 2 In this case reference Value 1 at $A$4 and -1 at the end of the Row offset calculation A 1 2 3 4 Value 1 5 Value 2 The other issue could be the COUNTA in relation to the list of values... COUNTA counts non blanks COUNT counts numbers What are your values? But instead of copying from the start of the list at Sheet1 cell B11 it is starting at cell B13. Also, if I copy the first cell on sheet 2 using this formula at B10 across the entire row of 30 cells being used it doesn't repeat the current list of 6 items. There are 14 blank cells, then a cell containing a 0 then the list starts repeating but from B13 again. Any ideas why this is or what I am doing wrong? Thanks |
#13
|
|||
|
|||
Copying a column list from one sheet to another and repeating?
The values in all list cells on sheet 1 are text. I don't know if it makes
any difference but the content of the original list cells are calculated by another formula. So there is a formula entered for every blank and filled cell in the list range in sheet 1. I changed the starting reference cell to B10 and added the -1 to the end and copied out to the 30 row cells but I still get a large number of blank cells and the list header is being repeated. "klswvu" wrote: The reference should be 1 cell above your first value you want to return or you need the -1 one at the end... this case reference the heading at $A$3 A 1 2 3 Heading 4 Value 1 5 Value 2 In this case reference Value 1 at $A$4 and -1 at the end of the Row offset calculation A 1 2 3 4 Value 1 5 Value 2 The other issue could be the COUNTA in relation to the list of values... COUNTA counts non blanks COUNT counts numbers What are your values? But instead of copying from the start of the list at Sheet1 cell B11 it is starting at cell B13. Also, if I copy the first cell on sheet 2 using this formula at B10 across the entire row of 30 cells being used it doesn't repeat the current list of 6 items. There are 14 blank cells, then a cell containing a 0 then the list starts repeating but from B13 again. Any ideas why this is or what I am doing wrong? Thanks |
#14
|
|||
|
|||
Copying a column list from one sheet to another and repeating?
The formula will effect the result...
Need to think about a work around... sorry "simon" wrote: The values in all list cells on sheet 1 are text. I don't know if it makes any difference but the content of the original list cells are calculated by another formula. So there is a formula entered for every blank and filled cell in the list range in sheet 1. I changed the starting reference cell to B10 and added the -1 to the end and copied out to the 30 row cells but I still get a large number of blank cells and the list header is being repeated. "klswvu" wrote: The reference should be 1 cell above your first value you want to return or you need the -1 one at the end... this case reference the heading at $A$3 A 1 2 3 Heading 4 Value 1 5 Value 2 In this case reference Value 1 at $A$4 and -1 at the end of the Row offset calculation A 1 2 3 4 Value 1 5 Value 2 The other issue could be the COUNTA in relation to the list of values... COUNTA counts non blanks COUNT counts numbers What are your values? But instead of copying from the start of the list at Sheet1 cell B11 it is starting at cell B13. Also, if I copy the first cell on sheet 2 using this formula at B10 across the entire row of 30 cells being used it doesn't repeat the current list of 6 items. There are 14 blank cells, then a cell containing a 0 then the list starts repeating but from B13 again. Any ideas why this is or what I am doing wrong? Thanks |
#15
|
|||
|
|||
Copying a column list from one sheet to another and repeating?
=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 |
#16
|
|||
|
|||
Copying a column list from one sheet to another and repeating?
Thanks for your continued help with this. I will test it and let you know the
result. "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 |
#17
|
|||
|
|||
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 |
#18
|
|||
|
|||
Copying a column list from one sheet to another and repeating?
=OFFSET(Sheet1!$A$2,((MOD(COLUMN(),(COUNTA(Sheet1! $A$3:$A$10)-(COUNTBLANK(Sheet1!$A$3:$A$10))))=1)*(COUNTA(Sheet 1!$A$3:$A$10)-(COUNTBLANK(Sheet1!$A$3:$A$10))))+((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(),(C OUNTA(Sheet1!$A$3:$A$10)-(COUNTBLANK(Sheet1!$A$3:$A$10))))-1),0)
It is a mess... but it is working with my ranges being the same as yours. "simon" wrote: |
#19
|
|||
|
|||
Copying a column list from one sheet to another and repeating?
Thanks again, I tried this formula...
=OFFSET(Sheet1!$A$2,((MOD(COLUMN(),(COUNTA(Sheet1! $A$3:$A$10)-(COUNTBLANK(Sheet1!$A$3:$A$10))))=1)*(COUNTA(Sheet 1!$A$3:$A$10)-(COUNTBLANK(Sheet1!$A$3:$A$10))))+((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(),(C OUNTA(Sheet1!$A$3:$A$10)-(COUNTBLANK(Sheet1!$A$3:$A$10))))-1),0) Which did copy the cells in the range from sheet 1 as long as all cels in the specified range were filled. If one or more of the cells is empty I get an error message. Is there any way to allow blank cells in the list range? Thanks "klswvu" wrote: =OFFSET(Sheet1!$A$2,((MOD(COLUMN(),(COUNTA(Sheet1! $A$3:$A$10)-(COUNTBLANK(Sheet1!$A$3:$A$10))))=1)*(COUNTA(Sheet 1!$A$3:$A$10)-(COUNTBLANK(Sheet1!$A$3:$A$10))))+((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(),(C OUNTA(Sheet1!$A$3:$A$10)-(COUNTBLANK(Sheet1!$A$3:$A$10))))-1),0) It is a mess... but it is working with my ranges being the same as yours. "simon" wrote: |
|
Thread Tools | |
Display Modes | |
|
|