How do I overlap numbers beyond column width (with a twist)?

[Drillbit]

First of all Howdy! I just found this board - what a great resource!

My problem is this: I cannot increase my column width, but want my numbers to remain visible. I did this and it works beautifully:

=TEXT(DY5,"$#.#")

My problem, is that the number I want to pull is actually nested in an IF statement:

=IF(ISTEXT(EF16),TEXT(DY5,"$#.#"),"")

The problem is that the IF statement puts "" into adjacent cells and overlaps my number that I am trying to post.
So the question is, how can I keep those adjacent cells clear of contents by using the IF statement?

Thanks in advance!

 

[shades]

Do you mean that the numbers push into the the next cell? If so, then just adjust the column width. You can select the column, go to the menu bar, Format > Column > Autofit.

 

[Drillbit]

Re: How do I overlap numbers beyond column width (with a twi

Well, I cannot adjust the column widths. The sheet is set up like a daily calendar, and it would make some days much wider than others and look all jaberwoky. Any other ideas?

 

[Seti]

Re: How do I overlap numbers beyond column width (with a twi

Well, the problem is that if you want the text to flow over into the next cell, then the next cell must be blank. This means that it can't have a formula in it. Therefore, you would need to have the calendar populated by VBA code.

How about allowing the cell to wrap to multiple lines? Would that be an alternative? Could you format your numbers as 1000's to shorten them?

 

[mooseman]

Re: How do I overlap numbers beyond column width (with a twi

This VB code will remove the formulas from the cell that are empty or have " " from the if statement. You would have to repopulate the cells with the formula everytime you update. Could be done through VB code and made into an update macro. This won't help if the adjacent cells both have a value from your if statement.

For Each c In Selection
If c.value = "" Or c.value = " " Then
c.ClearContents
End If
Next c


Hope this helps.