Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP and Wrap Text
Hi,
Im using a VLOOKUP to display text in different worksheets in the same workbook. The trouble Im having is getting the data to display exactly as it does in the reference worksheet. Single cells with a single line of text show correctly, however, if the text in the single cell is greater than one line or contains bulleted lines, then the returns in the other worksheets either show a line of hash symbols (##########) or just the first line of text. I have used a General format for all the cells on all the worksheets and the €˜Wrap Text checkbox is ticked. I would therefore expect the row height of the cells that contain the VLOOKUP formula to change so it displays all the reference data but it doesnt. Will I have to preset the row heights on the other worksheets so that the data displays correctly? Im using Excel 2k3 DK |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP and Wrap Text
The only time I've seen those ###### symbols with text values is when I have the
cell formatted as Text and the length of the text is between 256 and 1024 characters. Did you convert your formulas to values and then format the cell as text? If you did, try formatting those offending cells as General. As for the rowheight stuff, excel won't change the rowheight based on the results of a formula. But you could use an event macro to do it for you: If you want to try, right click on the worksheet that should have this behavior. Select View code. Paste this into the code window: Option Explicit Private Sub Worksheet_Calculate() application.enableevents = false Me.Rows.AutoFit 'or be specific Me.Rows("1:33").AutoFit application.enableevents = true End Sub Changing the rowheight in some versions of excel (xl2003+, IIRC) will cause the excel to want to recalc again. The .enableevents stuff stops excel from going into a loop--recalc, loop, recalc, loop, .... DK wrote: Hi, Im using a VLOOKUP to display text in different worksheets in the same workbook. The trouble Im having is getting the data to display exactly as it does in the reference worksheet. Single cells with a single line of text show correctly, however, if the text in the single cell is greater than one line or contains bulleted lines, then the returns in the other worksheets either show a line of hash symbols (##########) or just the first line of text. I have used a General format for all the cells on all the worksheets and the €˜Wrap Text checkbox is ticked. I would therefore expect the row height of the cells that contain the VLOOKUP formula to change so it displays all the reference data but it doesnt. Will I have to preset the row heights on the other worksheets so that the data displays correctly? Im using Excel 2k3 DK -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP and Wrap Text
Dave,
Thanks for your help and prompt reply, I used the General format to get rid of the hash symbols, but unfortunately the event macro doesn't quite fit the bill so I'll just have to settle for predetermined row heights for now. The macro condenses the row height to a single line rather than increase it to fit the text. Kinda back to square one but never mind. DK "Dave Peterson" wrote: The only time I've seen those ###### symbols with text values is when I have the cell formatted as Text and the length of the text is between 256 and 1024 characters. Did you convert your formulas to values and then format the cell as text? If you did, try formatting those offending cells as General. As for the rowheight stuff, excel won't change the rowheight based on the results of a formula. But you could use an event macro to do it for you: If you want to try, right click on the worksheet that should have this behavior. Select View code. Paste this into the code window: Option Explicit Private Sub Worksheet_Calculate() application.enableevents = false Me.Rows.AutoFit 'or be specific Me.Rows("1:33").AutoFit application.enableevents = true End Sub Changing the rowheight in some versions of excel (xl2003+, IIRC) will cause the excel to want to recalc again. The .enableevents stuff stops excel from going into a loop--recalc, loop, recalc, loop, .... DK wrote: Hi, I'm using a VLOOKUP to display text in different worksheets in the same workbook. The trouble I'm having is getting the data to display exactly as it does in the reference worksheet. Single cells with a single line of text show correctly, however, if the text in the single cell is greater than one line or contains bulleted lines, then the returns in the other worksheets either show a line of hash symbols (##########) or just the first line of text. I have used a General format for all the cells on all the worksheets and the 'Wrap Text' checkbox is ticked. I would therefore expect the row height of the cells that contain the VLOOKUP formula to change so it displays all the reference data but it doesn't. Will I have to preset the row heights on the other worksheets so that the data displays correctly? I'm using Excel 2k3 DK -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP and Wrap Text
The macro will work if you're not using merged cells.
DK wrote: Dave, Thanks for your help and prompt reply, I used the General format to get rid of the hash symbols, but unfortunately the event macro doesn't quite fit the bill so I'll just have to settle for predetermined row heights for now. The macro condenses the row height to a single line rather than increase it to fit the text. Kinda back to square one but never mind. DK "Dave Peterson" wrote: The only time I've seen those ###### symbols with text values is when I have the cell formatted as Text and the length of the text is between 256 and 1024 characters. Did you convert your formulas to values and then format the cell as text? If you did, try formatting those offending cells as General. As for the rowheight stuff, excel won't change the rowheight based on the results of a formula. But you could use an event macro to do it for you: If you want to try, right click on the worksheet that should have this behavior. Select View code. Paste this into the code window: Option Explicit Private Sub Worksheet_Calculate() application.enableevents = false Me.Rows.AutoFit 'or be specific Me.Rows("1:33").AutoFit application.enableevents = true End Sub Changing the rowheight in some versions of excel (xl2003+, IIRC) will cause the excel to want to recalc again. The .enableevents stuff stops excel from going into a loop--recalc, loop, recalc, loop, .... DK wrote: Hi, I'm using a VLOOKUP to display text in different worksheets in the same workbook. The trouble I'm having is getting the data to display exactly as it does in the reference worksheet. Single cells with a single line of text show correctly, however, if the text in the single cell is greater than one line or contains bulleted lines, then the returns in the other worksheets either show a line of hash symbols (##########) or just the first line of text. I have used a General format for all the cells on all the worksheets and the 'Wrap Text' checkbox is ticked. I would therefore expect the row height of the cells that contain the VLOOKUP formula to change so it displays all the reference data but it doesn't. Will I have to preset the row heights on the other worksheets so that the data displays correctly? I'm using Excel 2k3 DK -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP and Wrap Text
Ahhh, that might be the problem then. It will only be used on cells that are
merged. "Dave Peterson" wrote: The macro will work if you're not using merged cells. DK wrote: Dave, Thanks for your help and prompt reply, I used the General format to get rid of the hash symbols, but unfortunately the event macro doesn't quite fit the bill so I'll just have to settle for predetermined row heights for now. The macro condenses the row height to a single line rather than increase it to fit the text. Kinda back to square one but never mind. DK "Dave Peterson" wrote: The only time I've seen those ###### symbols with text values is when I have the cell formatted as Text and the length of the text is between 256 and 1024 characters. Did you convert your formulas to values and then format the cell as text? If you did, try formatting those offending cells as General. As for the rowheight stuff, excel won't change the rowheight based on the results of a formula. But you could use an event macro to do it for you: If you want to try, right click on the worksheet that should have this behavior. Select View code. Paste this into the code window: Option Explicit Private Sub Worksheet_Calculate() application.enableevents = false Me.Rows.AutoFit 'or be specific Me.Rows("1:33").AutoFit application.enableevents = true End Sub Changing the rowheight in some versions of excel (xl2003+, IIRC) will cause the excel to want to recalc again. The .enableevents stuff stops excel from going into a loop--recalc, loop, recalc, loop, .... DK wrote: Hi, I'm using a VLOOKUP to display text in different worksheets in the same workbook. The trouble I'm having is getting the data to display exactly as it does in the reference worksheet. Single cells with a single line of text show correctly, however, if the text in the single cell is greater than one line or contains bulleted lines, then the returns in the other worksheets either show a line of hash symbols (##########) or just the first line of text. I have used a General format for all the cells on all the worksheets and the 'Wrap Text' checkbox is ticked. I would therefore expect the row height of the cells that contain the VLOOKUP formula to change so it displays all the reference data but it doesn't. Will I have to preset the row heights on the other worksheets so that the data displays correctly? I'm using Excel 2k3 DK -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP and Wrap Text
Jim Rech wrote a macro called AutoFitMergedCellRowHeight that you may like:
http://groups.google.com/groups?thre...%40tkmsftngp05 DK wrote: Ahhh, that might be the problem then. It will only be used on cells that are merged. "Dave Peterson" wrote: The macro will work if you're not using merged cells. DK wrote: Dave, Thanks for your help and prompt reply, I used the General format to get rid of the hash symbols, but unfortunately the event macro doesn't quite fit the bill so I'll just have to settle for predetermined row heights for now. The macro condenses the row height to a single line rather than increase it to fit the text. Kinda back to square one but never mind. DK "Dave Peterson" wrote: The only time I've seen those ###### symbols with text values is when I have the cell formatted as Text and the length of the text is between 256 and 1024 characters. Did you convert your formulas to values and then format the cell as text? If you did, try formatting those offending cells as General. As for the rowheight stuff, excel won't change the rowheight based on the results of a formula. But you could use an event macro to do it for you: If you want to try, right click on the worksheet that should have this behavior. Select View code. Paste this into the code window: Option Explicit Private Sub Worksheet_Calculate() application.enableevents = false Me.Rows.AutoFit 'or be specific Me.Rows("1:33").AutoFit application.enableevents = true End Sub Changing the rowheight in some versions of excel (xl2003+, IIRC) will cause the excel to want to recalc again. The .enableevents stuff stops excel from going into a loop--recalc, loop, recalc, loop, .... DK wrote: Hi, I'm using a VLOOKUP to display text in different worksheets in the same workbook. The trouble I'm having is getting the data to display exactly as it does in the reference worksheet. Single cells with a single line of text show correctly, however, if the text in the single cell is greater than one line or contains bulleted lines, then the returns in the other worksheets either show a line of hash symbols (##########) or just the first line of text. I have used a General format for all the cells on all the worksheets and the 'Wrap Text' checkbox is ticked. I would therefore expect the row height of the cells that contain the VLOOKUP formula to change so it displays all the reference data but it doesn't. Will I have to preset the row heights on the other worksheets so that the data displays correctly? I'm using Excel 2k3 DK -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I get date/time to wrap (format - wrap text doesn't work)? | Excel Discussion (Misc queries) | |||
Wrap text doesn't wrap | Excel Worksheet Functions | |||
Why won't text in cell wrap. Cell format set to wrap. | Excel Discussion (Misc queries) | |||
Text not continuing to wrap for large block of text in Excel cell | Charts and Charting in Excel | |||
text (3750 char.)truncates with text wrap and row heigh adjusted? | Excel Discussion (Misc queries) |