Copying a column list from one sheet to another and repeating???

[simon]

Hi

Does anyone know how to copy a list of text in a column from one sheet and
display it in another sheet but then continue to repeat the original list?

I am trying to get the content from these cells to be displayed in a row of
cells but
the crucial thing is that I need the cells to repeat the list from the
beginning once a blank cell is reached in the original column. I'll try an
example to make this clearer...

1. There is Sheet A with column A1:A20 containing the names of 20 products.
2. There is Sheet B with row A1:AN1 containing 40 cells

In this example Sheet B should copy all the 20 product names then get to
cell 21 and find a blank cell at 'Sheet1:A21' so start the list from the
beginning again. This should occur no matter how many cells are calling the
original list of 20 products but as soon as it finds a blank cell at the end
of the list of 20 products it should go back to the beginning and start
displaying from cell 1. Please note that there should be no limit on the
number of cells in the original product list and the cells displaying the
copied list should be able to be displayed in any row on sheet 2.

Hopefully I've made it easier to understand and not harder. Any ideas would
be much appreciated.

Thanks
Simon

 

[klswvu]

=OFFSET(Sheet1!$A$1,IF((MOD(COLUMN(),$A$1)=0),(COUNTA(Sheet1!$A:$A)-1),((MOD(COLUMN(),$A$1))-1)),0)

 

[klswvu]

=OFFSET(Sheet1!$A$1,((MOD(COLUMN(),(COUNTA(Sheet1!$A:$A)))=0)*6)+(MOD(COLUMN(),(COUNTA(Sheet1!$A:$A))))-1,0)

Better... eliminates the IF

 

[klswvu]

=OFFSET(Sheet1!$A$1,((MOD(COLUMN(),(COUNTA(Sheet1!$A:$A)))=0)*(COUNTA(Sheet1!$A:$A)))+(MOD(COLUMN(),(COUNTA(Sheet1!$A:$A))))-1,0)

error fixed... the 6 should not be hard coded

 

[simon]

Thanks klswvu, one thing I didn't mention is that the column containing the
list on sheet 1 does have other content above it so the forumla would need to
specify which cell in the column the list starts from. Is that possible?

 

[klswvu]

Thanks klswvu, one thing I didn't mention is that the column containing the
list on sheet 1 does have other content above it so the forumla would need
to
specify which cell in the column the list starts from. Is that possible?

Yes...

Sheet1!$A$1 is the top value of the range it can be anything ie Sheet1!$A$20

COUNTA(Sheet1!$A:$A) needs to be modified ie COUNTA(Sheet1!$A$20:$A$100) or
you can make it a defined dynamic named range.

=OFFSET(Sheet1!$A$1,((MOD(COLUMN(),(COUNTA(Sheet1!$A:$A)))=0)*(COUNTA(Sheet1!$A:$A)))+(MOD(COLUMN(),(COUNTA(Sheet1!$A:$A))))-1,0)

 

[simon]

I used this formula but the list heading text is still being repeated with
the list contents...

=OFFSET('Sheet 1'!$A$2,((MOD(COLUMN(),(COUNTA('Sheet
1'!$A$2:$A$10)))=0)*(COUNTA('Sheet
1'!$A$2:$A$10)))+(MOD(COLUMN(),(COUNTA('Sheet 1'!$A$2:$A$10))))-1,0)

 

[klswvu]

I used this formula but the list heading text is still being repeated with

the list contents...

Remove the -1 at the end if you have a Heading.

 

[klswvu]

=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(),(COUNTA('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

 

[simon]

Thanks for that, I used this formula...

=OFFSET(Sheet1!$A$6,((MOD(COLUMN(),(COUNTA(Sheet1!$A$6:$A$10)))=0)*(COUNTA(Sheet1!$A$6:$A$10)))+(MOD(COLUMN(),(COUNTA(Sheet1!$A$6:$A$10)))),0)

But got an 'error in value' message. The first item in the list on sheet 1
is at A6 and I entered the formula in cell B5 on sheet 2.

 

[simon]

I tried this formula you suggested...

=OFFSET('Sheet1'!$B$11,((MOD(COLUMN(),(COUNTA('Sheet1'!$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]

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?

 

[simon]

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]

The formula will effect the result...
Need to think about a work around... sorry

 

[klswvu]

=OFFSET(Sheet1!$A$2,((MOD(COLUMN(),(COUNTA(Sheet1!$A$3:$A$10)-(COUNTBLANK(Sheet1!$A$3:$A$10))))=0)*(COUNTA(Sheet1!$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)


$A$2 is the heading and anchor for the offset
$A$3:$A$10 is the range where the text is pulled from

 

[simon]

Thanks for your continued help with this. I will test it and let you know the
result.

 

[simon]

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]

=OFFSET(Sheet1!$A$2,((MOD(COLUMN(),(COUNTA(Sheet1!$A$3:$A$10)-(COUNTBLANK(Sheet1!$A$3:$A$10))))=1)*(COUNTA(Sheet1!$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(Sheet1!$A$3:$A$10)-(COUNTBLANK(Sheet1!$A$3:$A$10))))+(MOD(COLUMN(),(COUNTA(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]

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(Sheet1!$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(Sheet1!$A$3:$A$10)-(COUNTBLANK(Sheet1!$A$3:$A$10))))+(MOD(COLUMN(),(COUNTA(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