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

Drillbit

New Member
Joined
Jan 18, 2005
Messages
12
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!
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

shades

Well-known Member
Joined
Mar 20, 2002
Messages
1,550
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

New Member
Joined
Jan 18, 2005
Messages
12
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

Well-known Member
Joined
May 19, 2002
Messages
2,916
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

Board Regular
Joined
Jul 23, 2004
Messages
195
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.
 

Forum statistics

Threads
1,147,680
Messages
5,742,590
Members
423,740
Latest member
JCF_

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top