A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old April 7th, 2009, 09:59 AM posted to microsoft.public.excel.worksheet.functions
Simon
external usenet poster
 
Posts: 368
Default Copying a column list from one sheet to another and repeating???

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

  #2  
Old April 7th, 2009, 04:56 PM posted to microsoft.public.excel.worksheet.functions
klswvu[_2_]
external usenet poster
 
Posts: 27
Default Copying a column list from one sheet to another and repeating???

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

"simon" wrote:

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

  #3  
Old April 7th, 2009, 06:23 PM posted to microsoft.public.excel.worksheet.functions
klswvu[_2_]
external usenet poster
 
Posts: 27
Default Copying a column list from one sheet to another and repeating?

=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" wrote:

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


  #4  
Old April 7th, 2009, 06:58 PM posted to microsoft.public.excel.worksheet.functions
klswvu[_2_]
external usenet poster
 
Posts: 27
Default Copying a column list from one sheet to another and repeating?

=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


  #5  
Old April 8th, 2009, 10:35 AM posted to microsoft.public.excel.worksheet.functions
Simon
external usenet poster
 
Posts: 368
Default Copying a column list from one sheet to another and repeating?

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" wrote:

=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


  #6  
Old April 8th, 2009, 06:53 PM posted to microsoft.public.excel.worksheet.functions
klswvu[_2_]
external usenet poster
 
Posts: 27
Default Copying a column list from one sheet to another and repeating?

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)

  #7  
Old April 8th, 2009, 07:37 PM posted to microsoft.public.excel.worksheet.functions
Simon
external usenet poster
 
Posts: 368
Default Copying a column list from one sheet to another and repeating?

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" wrote:

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)

  #8  
Old April 8th, 2009, 08:36 PM posted to microsoft.public.excel.worksheet.functions
klswvu[_2_]
external usenet poster
 
Posts: 27
Default Copying a column list from one sheet to another and repeating?

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.


  #9  
Old April 8th, 2009, 08:52 PM posted to microsoft.public.excel.worksheet.functions
klswvu[_2_]
external usenet poster
 
Posts: 27
Default Copying a column list from one sheet to another and repeating?

=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


  #10  
Old April 8th, 2009, 10:03 PM posted to microsoft.public.excel.worksheet.functions
Simon
external usenet poster
 
Posts: 368
Default Copying a column list from one sheet to another and repeating?

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.

"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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:39 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.