Troubleshoot help

[Whitney]

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.

 

[curious engineer]

Maybe the column widths needs to be adjusted for the cell to display how you
want it to.

 

[Whitney]

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?

 

[Gord Dibben]

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