You may download the test file I used to see what you are doing different...
Here is the download link valid for next seven days;
http://wikisend.com/download/534452/RepeatWords.xls

"simon" wrote:

Hi, thanks for your response. I tried the formula but I get a #value error
message. If I change the first cell reference from $A$11 to $A$10 it displays
the list heading but thats as near as I could get.

Any ideas?

Tahnks

"Sheeloo" wrote:

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

Change 40 to 10+ (number of maximum keywords in your list, assumed to be 30
in the above formula)

It will repeat from the beginning after the non-blank entries... there
should not be any blanks between entries.

"simon" wrote:

Hi, I have been struggling with what seems like a simple problem to solve for
over two months and two very generous members of this forum have tried their
best to offer solutions but it's still not working.

Can anyone solve this...

I have a workbook with two sheets. Sheet 1 lists two columns of keyword
lists in columns A and B. These lists start at cells A10 and B10 with the
list heading titles and the first list itemts in cells A11 and B11. The lists
then continue down for a varying number of cells. This list is automatically
generated from the following formula using entries from the same sheet and
named lists...

=IF(ROWS(A$11:A11)<=SUM(COUNTIF(RootStatus,Rating) ),INDEX(Root,SMALL(IF(RootStatus=Rating,ROW(Root)) ,ROWS(A$11:A11))-MIN(ROW(Root))+1),"")

The result I need is to copy the keywords from one list in Sheet 1, say in
column A11 down, to a row of 30 cells (B15:AE15) in Sheet 2. I also need the
copied list to be repeated if there are less than 30 keywords in the original
list from Sheet 1.

The best formula suggestion I have been given so far is this...

=OFFSET('Sheet 1'!$A$11,((MOD(COLUMN(),(COUNTA('Sheet
1'!$A$11:$A$200)-(COUNTBLANK('Sheet 1'!$A$11:$A$200))))=0)*(COUNTA('Sheet
1'!$A$11:$A$200)-(COUNTBLANK('Sheet
1'!$A$11:$A$200)))+(MOD(COLUMN(),(COUNTA('Sheet
1'!$A$11:$A$200)-(COUNTBLANK('Sheet 1'!$A$11:$A$200))))))-1,0)

This formula works fine but it misses the first keyword in the list at cell
A11. The first keyword is included when the list is repeated, just not at
the start. Also, if I try to extend the range of cells in the list column
from A$11:$A$20 to anything above A$11:$A$35 I get DIV and REF errors.

Any ideas on how to solve this would be grately appreciated to say the least.

Many thanks