Is this really not possible??

S

simon

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
 
S

Sheeloo

Try
=OFFSET(Sheet1!$A$10,IF(MOD(COLUMN()-1,COUNTA(Sheet1!$A$11:$A$40))=0,COUNTA(Sheet1!$A$11:$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.
 
S

Sheeloo

Try
=OFFSET(Sheet1!$A$10,IF(MOD(COLUMN()-1,COUNTA(Sheet1!$A$11:$A$40))=0,COUNTA(Sheet1!$A$11:$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.
 
S

simon

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
 
S

simon

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
 
S

simon

Thanks Sheelo, I downloaded the file and compared your spreadsheet to mine. I
think my problem is being caused by the formula that creates the two lists on
sheet1...

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

If I copy the formula down from cell A11 to A30 but say only A11 to A20
contains any content, the repeat of these cells is blank for 10 cells on
Sheet 2. Then the repeat starts again. If I delete the formula from the blank
cells (i.e. A21:A30) your formula repeats the list content fine.

Any idea how I can fix this?

Thanks again
 
S

simon

Thanks Sheelo, I downloaded the file and compared your spreadsheet to mine. I
think my problem is being caused by the formula that creates the two lists on
sheet1...

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

If I copy the formula down from cell A11 to A30 but say only A11 to A20
contains any content, the repeat of these cells is blank for 10 cells on
Sheet 2. Then the repeat starts again. If I delete the formula from the blank
cells (i.e. A21:A30) your formula repeats the list content fine.

Any idea how I can fix this?

Thanks again
 
S

Sheeloo

See the updated file at
http://wikisend.com/download/543218/RepeatWords.xls

Basically COUNTA(Sheet1!$A$11:$A$40) was used in the formula to count the no
of key words.
I have put a formula (=COUNTA(A11:A80)-COUNTIF(A11:A80,0)) to do that in B10
of Sheet1 and used that in the formula
=OFFSET(Sheet1!$A$10,IF(MOD(COLUMN()-1,Sheet1!$B$10)=0,Sheet1!$B$10,MOD(COLUMN()-1,Sheet1!$B$10)),0)
 
S

Sheeloo

See the updated file at
http://wikisend.com/download/543218/RepeatWords.xls

Basically COUNTA(Sheet1!$A$11:$A$40) was used in the formula to count the no
of key words.
I have put a formula (=COUNTA(A11:A80)-COUNTIF(A11:A80,0)) to do that in B10
of Sheet1 and used that in the formula
=OFFSET(Sheet1!$A$10,IF(MOD(COLUMN()-1,Sheet1!$B$10)=0,Sheet1!$B$10,MOD(COLUMN()-1,Sheet1!$B$10)),0)
 
S

simon

Thanks, I downloaded the file again but the formula looks the same and gives
me the same problems as before.
 
S

simon

Thanks, I downloaded the file again but the formula looks the same and gives
me the same problems as before.
 
S

Sheeloo

Can you mail the file to me or upload to wikisend.com and paste the link
here... ?
Do remove any personal/confidential information.
 
S

Sheeloo

Can you mail the file to me or upload to wikisend.com and paste the link
here... ?
Do remove any personal/confidential information.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Is this possible?? 3
Run recalc until a specified vaule is reached. 3
If/Then function in excel 3
Find the first & subsequent cells to contain text in a column & re 28
Figuring out a lengthy formula. Probably quite simple... 3
LOOKUP function between two dates in Excel 2007 6
LOOKUP within a 1
Auto re-sorting of data- HELP - Is this possible ?? 4

Top