Hello, Using Excel 2003, but would also like an Excel 2007 solution if different.
Thank you to anyone who can consider helping with this complicated formula that I hope will solve a problem. Please bear with me on this long explaination. I cannot begin to put together the formula needed to do what I want here, but I beleive it is doable.
If this is possible, I am looking for a formula to create a list to use in a named range for a data validation list, but it gets a bit complicated and I hope everyone will take a look at this description and see if there is a solution to make this idea work. If not possible, I will try to look for a VBA solution, but I am hoping this is workable.
The general idea is to take text in several rows of cells (in a single column) from one list (List1 – Can change) and create a second list (list2) that will “wrap” the text from list1 onto several lines (separate consecutive rows). I am looking for this to solve the validation issue that seems to plaque a lot of people, were a data validation list will not wrap text. I haven’t seen a good solution; nothing that I have seen is really that acceptable, maybe someone else has, but I haven’t found it. I do not have the option of zooming the cell, or expanding it, etc, which is used by some to solve the problem - the cell with the validation list has text wrap and can only be as big as it is, and the dropdown list items can be quite long (the user can enter their items/narrative in List1, as the problem goes, you can only see so much of the list items because you can’t get the text to wrap in the dropdown list (unless I missed something – if I have please let me know).
Here then is what I would like to do - it seems like it is doable. List1, where the user enters narrative is 30 rows long (and actually there are several of these lists for user input), and I would establish another area, List2 that would be about 100 or more rows long, where any formula would be copied down. This is the area that would actually be used by the data validation list. In the list2 area, the formula would look at the narrative in row 1 of List1, and begin to parse the narrative into several rows, as necessary.
For example, based on my column size of the cell with the data validation dropdown list, I think about 60 characters would be visible. So, if the narrative in row 1/List1 is greater that 60 characters, row 1/List2 would contain the first 60 characters. Then row2/List2 would have the next 60 characters, and row 3 the next 60 characters etc (I think that the most lines on List2 needed for each row in List1 would be three or four).
If possible, to further complicate things, for the list to be easily viewable in the data validation dropdown, I would like the second line and third line, etc, of List2, to be indented, so I would like three or four spaces to be inserted in front of the text. AND, if I could have all of my wishes, again for ease of viewing the dropdown list, I would like a blank line inserted on List2 after each row/cell/text from List1 has been completed. Plus, I think I can use the blank line to help associate each of the separate lines form List2, back to the original single line narrative in List1 for insertion by the data validation list.
So as an example, this line of text from List1:
“If possible, to further complicate things, for the list to be easily viewable in the data validation dropdown, I would like the second line and third line, etc, of List2”
Would look like this on separate rows:
If possible, to further complicate things, for the list to b
Indent 3 spaces then: e easily viewable in the data validation dropdown, I would l
Indent 3 spaces then: ike the second line and third line, etc, of List2
Next row/cell: blank
Next row /cell: Start next row/narrative from List1
I know the above example creates the clean break in words based on a character count, but I’m not sure what else would be doable unless things got really complicate formula-wise. One solution may be to have a dash “-“ inserted after each line via the formula. To do this of course, the number of actual characters used from the row/List1, would need to be reduced by a total of four characters - 3 for the spaces/indent and one for the dash. If anyone knows of a way to make the break by word, that would be great.
So any formula that could do that, would need to calculate haw many characters there are in each row, how many lines it will requires to parse it out, plus a blank row, then start to wrap the next row of narrative. I am guessing with LEN, LEFT, MID, ROW, REPT, INDEX, MATCH, SUMPRODUCT, VLOOKUP, that something like this could be constructed. I am guessing you can first identify how many items are in the list, then for each item capture the length of the narrative/item =Len(), then parse it out with Left(), and Mid(), and somehow add the spaces to the front REPT()? of the second and third, and at most, a fourth row, then once that is all known, create the next row as blank (“”), then go to the next item in the list.
I manually created a list for data validation to look like this and it looks just like the text is wrapped so you can distinguish between items. If there is a way to create the list to look like this, then the next challenge would be to somehow have the list in the dropdown list actually enter all of the narrative in the cell, possibly by each line in the dropdown list being associated with the first list where the narrative comes from (List1), maybe an Indirect function, or another formula next to each cell to association each line, no matter which one is clicked, to enter all of the original text/narrative from List1.
If you have stuck with me this far and have some ideas and believe it is workable, I think it will be a good solution for the dropdown list wrap issue.
Thank you for the consideration and let me know an ideas you may have or if I need to clarify any points.
Thank you to anyone who can consider helping with this complicated formula that I hope will solve a problem. Please bear with me on this long explaination. I cannot begin to put together the formula needed to do what I want here, but I beleive it is doable.
If this is possible, I am looking for a formula to create a list to use in a named range for a data validation list, but it gets a bit complicated and I hope everyone will take a look at this description and see if there is a solution to make this idea work. If not possible, I will try to look for a VBA solution, but I am hoping this is workable.
The general idea is to take text in several rows of cells (in a single column) from one list (List1 – Can change) and create a second list (list2) that will “wrap” the text from list1 onto several lines (separate consecutive rows). I am looking for this to solve the validation issue that seems to plaque a lot of people, were a data validation list will not wrap text. I haven’t seen a good solution; nothing that I have seen is really that acceptable, maybe someone else has, but I haven’t found it. I do not have the option of zooming the cell, or expanding it, etc, which is used by some to solve the problem - the cell with the validation list has text wrap and can only be as big as it is, and the dropdown list items can be quite long (the user can enter their items/narrative in List1, as the problem goes, you can only see so much of the list items because you can’t get the text to wrap in the dropdown list (unless I missed something – if I have please let me know).
Here then is what I would like to do - it seems like it is doable. List1, where the user enters narrative is 30 rows long (and actually there are several of these lists for user input), and I would establish another area, List2 that would be about 100 or more rows long, where any formula would be copied down. This is the area that would actually be used by the data validation list. In the list2 area, the formula would look at the narrative in row 1 of List1, and begin to parse the narrative into several rows, as necessary.
For example, based on my column size of the cell with the data validation dropdown list, I think about 60 characters would be visible. So, if the narrative in row 1/List1 is greater that 60 characters, row 1/List2 would contain the first 60 characters. Then row2/List2 would have the next 60 characters, and row 3 the next 60 characters etc (I think that the most lines on List2 needed for each row in List1 would be three or four).
If possible, to further complicate things, for the list to be easily viewable in the data validation dropdown, I would like the second line and third line, etc, of List2, to be indented, so I would like three or four spaces to be inserted in front of the text. AND, if I could have all of my wishes, again for ease of viewing the dropdown list, I would like a blank line inserted on List2 after each row/cell/text from List1 has been completed. Plus, I think I can use the blank line to help associate each of the separate lines form List2, back to the original single line narrative in List1 for insertion by the data validation list.
So as an example, this line of text from List1:
“If possible, to further complicate things, for the list to be easily viewable in the data validation dropdown, I would like the second line and third line, etc, of List2”
Would look like this on separate rows:
If possible, to further complicate things, for the list to b
Indent 3 spaces then: e easily viewable in the data validation dropdown, I would l
Indent 3 spaces then: ike the second line and third line, etc, of List2
Next row/cell: blank
Next row /cell: Start next row/narrative from List1
I know the above example creates the clean break in words based on a character count, but I’m not sure what else would be doable unless things got really complicate formula-wise. One solution may be to have a dash “-“ inserted after each line via the formula. To do this of course, the number of actual characters used from the row/List1, would need to be reduced by a total of four characters - 3 for the spaces/indent and one for the dash. If anyone knows of a way to make the break by word, that would be great.
So any formula that could do that, would need to calculate haw many characters there are in each row, how many lines it will requires to parse it out, plus a blank row, then start to wrap the next row of narrative. I am guessing with LEN, LEFT, MID, ROW, REPT, INDEX, MATCH, SUMPRODUCT, VLOOKUP, that something like this could be constructed. I am guessing you can first identify how many items are in the list, then for each item capture the length of the narrative/item =Len(), then parse it out with Left(), and Mid(), and somehow add the spaces to the front REPT()? of the second and third, and at most, a fourth row, then once that is all known, create the next row as blank (“”), then go to the next item in the list.
I manually created a list for data validation to look like this and it looks just like the text is wrapped so you can distinguish between items. If there is a way to create the list to look like this, then the next challenge would be to somehow have the list in the dropdown list actually enter all of the narrative in the cell, possibly by each line in the dropdown list being associated with the first list where the narrative comes from (List1), maybe an Indirect function, or another formula next to each cell to association each line, no matter which one is clicked, to enter all of the original text/narrative from List1.
If you have stuck with me this far and have some ideas and believe it is workable, I think it will be a good solution for the dropdown list wrap issue.
Thank you for the consideration and let me know an ideas you may have or if I need to clarify any points.
