Hi

Does anyone know how to display content from sequential cells in a column
which is in another sheet in the same workbook and then continue repeat this?

I need 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 'SheetA!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.

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

Thanks
Simon

Put this formula in sheet2 in cell A1. Then copy across Row 1 to column AN

=INDEX(Sheet1!$A:$A,MOD(COLUMN()-1,COUNTA(Sheet1!$A:$A))+1,1)

The formual assumes ther is only the data you want in column A with nothing
after the last character. The formula uses index(Reference Cell, Row,
Column) Where Reference is Sheet1!A1 and the column is always 1. the row
Number isn sheet 1 is the same as the column number in sheet 2. I use the
MOD function so when you get to the end of the data in Sheet 1 it repeats.

"pieman3" wrote:

Hi

Does anyone know how to display content from sequential cells in a column
which is in another sheet in the same workbook and then continue repeat this?

I need 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 'SheetA!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.

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

Thanks
Simon

Thanks Joel, will this still work if the row number in sheet2 is different
from the column number in sheet1?

Simon

"joel" wrote:

Put this formula in sheet2 in cell A1. Then copy across Row 1 to column AN

=INDEX(Sheet1!$A:$A,MOD(COLUMN()-1,COUNTA(Sheet1!$A:$A))+1,1)

The formual assumes ther is only the data you want in column A with nothing
after the last character. The formula uses index(Reference Cell, Row,
Column) Where Reference is Sheet1!A1 and the column is always 1. the row
Number isn sheet 1 is the same as the column number in sheet 2. I use the
MOD function so when you get to the end of the data in Sheet 1 it repeats.

"pieman3" wrote:

Hi

Does anyone know how to display content from sequential cells in a column
which is in another sheet in the same workbook and then continue repeat this?

I need 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 'SheetA!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.

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

Thanks
Simon

Yes, it only uses the column number on sheet 2.

"pieman3" wrote:

Thanks Joel, will this still work if the row number in sheet2 is different
from the column number in sheet1?

Simon

"joel" wrote:

Put this formula in sheet2 in cell A1. Then copy across Row 1 to column AN

=INDEX(Sheet1!$A:$A,MOD(COLUMN()-1,COUNTA(Sheet1!$A:$A))+1,1)

The formual assumes ther is only the data you want in column A with nothing
after the last character. The formula uses index(Reference Cell, Row,
Column) Where Reference is Sheet1!A1 and the column is always 1. the row
Number isn sheet 1 is the same as the column number in sheet 2. I use the
MOD function so when you get to the end of the data in Sheet 1 it repeats.

"pieman3" wrote:

Hi

Does anyone know how to display content from sequential cells in a column
which is in another sheet in the same workbook and then continue repeat this?

I need 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 'SheetA!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.

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

Thanks
Simon

Hi Joel

Is there a way to specify a specific column cell range on sheet1 as there is
other text in that column which is also being listed and repeated?

Thanks
Simon

"joel" wrote:

Yes, it only uses the column number on sheet 2.

"pieman3" wrote:

Thanks Joel, will this still work if the row number in sheet2 is different
from the column number in sheet1?

Simon

"joel" wrote:

Put this formula in sheet2 in cell A1. Then copy across Row 1 to column AN

=INDEX(Sheet1!$A:$A,MOD(COLUMN()-1,COUNTA(Sheet1!$A:$A))+1,1)

The formual assumes ther is only the data you want in column A with nothing
after the last character. The formula uses index(Reference Cell, Row,
Column) Where Reference is Sheet1!A1 and the column is always 1. the row
Number isn sheet 1 is the same as the column number in sheet 2. I use the
MOD function so when you get to the end of the data in Sheet 1 it repeats.

"pieman3" wrote:

Hi

Does anyone know how to display content from sequential cells in a column
which is in another sheet in the same workbook and then continue repeat this?

I need 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 'SheetA!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.

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

Thanks
Simon

Can you explain better?

If you want
sheet 2 Row 1 to be sheet 1 column A
sheet 2 Row 2 to be sheet 1 column B
sheet 2 Row 3 to be sheet 1 column C

this will work
=INDEX(Sheet1!$A:$A,MOD(COLUMN()-1,COUNTA(Sheet1!$A:$A))+1,Row())

I change the last 1 to Row()

"pieman3" wrote:

Hi Joel

Is there a way to specify a specific column cell range on sheet1 as there is
other text in that column which is also being listed and repeated?

Thanks
Simon

"joel" wrote:

Yes, it only uses the column number on sheet 2.

"pieman3" wrote:

Thanks Joel, will this still work if the row number in sheet2 is different
from the column number in sheet1?

Simon

"joel" wrote:

Put this formula in sheet2 in cell A1. Then copy across Row 1 to column AN

=INDEX(Sheet1!$A:$A,MOD(COLUMN()-1,COUNTA(Sheet1!$A:$A))+1,1)

The formual assumes ther is only the data you want in column A with nothing
after the last character. The formula uses index(Reference Cell, Row,
Column) Where Reference is Sheet1!A1 and the column is always 1. the row
Number isn sheet 1 is the same as the column number in sheet 2. I use the
MOD function so when you get to the end of the data in Sheet 1 it repeats.

"pieman3" wrote:

Hi

Does anyone know how to display content from sequential cells in a column
which is in another sheet in the same workbook and then continue repeat this?

I need 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 'SheetA!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.

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

Thanks
Simon