| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is this really not possible??
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Is this really not possible??
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Is this really not possible??
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Is this really not possible??
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Is this really not possible??
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 "Sheeloo" wrote: 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Is this really not possible??
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) "simon" wrote: 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 "Sheeloo" wrote: 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Is this really not possible??
Thanks, I downloaded the file again but the formula looks the same and gives
me the same problems as before. "Sheeloo" wrote: 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) "simon" wrote: 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 "Sheeloo" wrote: 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Is this really not possible??
Can you mail the file to me or upload to wikisend.com and paste the link
here... ? Do remove any personal/confidential information. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Is this really not possible??
Thanks, I have uploaded the spreadsheet here
http://wikisend.com/download/466386/Keyword Spreadsheet.xls "Sheeloo" wrote: Can you mail the file to me or upload to wikisend.com and paste the link here... ? Do remove any personal/confidential information. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Is this really not possible??
Hi Sheeloo, did you get chance to look at the file I uploaded?
Thanks "Sheeloo" wrote: Can you mail the file to me or upload to wikisend.com and paste the link here... ? Do remove any personal/confidential information. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
