Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Troubleshoot help
I have an excel spreadsheet that I'm using as a performance scorecard. I'm
calculating a weighted average with Sumproduct. In the range of cells for the sumproduct formula are cells with text. The text cells are merged to create a comments box. The issue is when you reach a certain number of characters in the comments box the sumproduct cell changes to #VALUE! Additionally, if the comments box is formatted as Text, when it reachs that same number of characters, the cell displays ####### across the entire merged cell. However, if the format is changed to General it displays the text again. What am I missing? I would like the comments box character limit to be unlimited or at least as long as it's still viewable, and without impacting the sumproduct cell value. It might be helpful if I could send you a copy of the scorecard. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Troubleshoot help
Maybe the column widths needs to be adjusted for the cell to display how you
want it to. "Whitney" wrote: I have an excel spreadsheet that I'm using as a performance scorecard. I'm calculating a weighted average with Sumproduct. In the range of cells for the sumproduct formula are cells with text. The text cells are merged to create a comments box. The issue is when you reach a certain number of characters in the comments box the sumproduct cell changes to #VALUE! Additionally, if the comments box is formatted as Text, when it reachs that same number of characters, the cell displays ####### across the entire merged cell. However, if the format is changed to General it displays the text again. What am I missing? I would like the comments box character limit to be unlimited or at least as long as it's still viewable, and without impacting the sumproduct cell value. It might be helpful if I could send you a copy of the scorecard. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Troubleshoot help
My concern is not necessarily how to see the info in the comments cell. I
understand how to adjust column widths for that. What my issue is, is that when you reach approximately 256 characters in the comments cell it impacts the Overall Score cell's value. I'm not sure why it is impacting that cell. At 255 character's in the comments cell, the cell with the sumproduct formula (Overall Score) displays the correct value, however at 256 characters it changes to #VALUE!. Why would it do this and how can I correct it? "curious engineer" wrote: Maybe the column widths needs to be adjusted for the cell to display how you want it to. "Whitney" wrote: I have an excel spreadsheet that I'm using as a performance scorecard. I'm calculating a weighted average with Sumproduct. In the range of cells for the sumproduct formula are cells with text. The text cells are merged to create a comments box. The issue is when you reach a certain number of characters in the comments box the sumproduct cell changes to #VALUE! Additionally, if the comments box is formatted as Text, when it reachs that same number of characters, the cell displays ####### across the entire merged cell. However, if the format is changed to General it displays the text again. What am I missing? I would like the comments box character limit to be unlimited or at least as long as it's still viewable, and without impacting the sumproduct cell value. It might be helpful if I could send you a copy of the scorecard. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Troubleshoot help
Excel has a problem with text-formatted cells when the number of characters is
between 255 and 1024. Format as General to keep the #### at bay. I would first of all get rid of the merged cells which cause no end of problems. Could you perhaps insert a regular Comment instead? Gord Dibben MS Excel MVP On Thu, 3 Apr 2008 09:46:02 -0700, Whitney wrote: I have an excel spreadsheet that I'm using as a performance scorecard. I'm calculating a weighted average with Sumproduct. In the range of cells for the sumproduct formula are cells with text. The text cells are merged to create a comments box. The issue is when you reach a certain number of characters in the comments box the sumproduct cell changes to #VALUE! Additionally, if the comments box is formatted as Text, when it reachs that same number of characters, the cell displays ####### across the entire merged cell. However, if the format is changed to General it displays the text again. What am I missing? I would like the comments box character limit to be unlimited or at least as long as it's still viewable, and without impacting the sumproduct cell value. It might be helpful if I could send you a copy of the scorecard. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Troubleshoot hiding columns | Setting up and Configuration of Excel | |||
troubleshoot a workbook for me? | Excel Discussion (Misc queries) | |||
troubleshoot absolute value | Excel Worksheet Functions | |||
Troubleshoot a bug in Excel | Excel Discussion (Misc queries) | |||
troubleshoot Highlight | Excel Discussion (Misc queries) |