Autofit Unmerged Cells (XP & Excel 2003)

[Gina]

My workbook has 3 tabs. The 3rd tab is a combination of the first two. In
the 3rd tab, an example of the formula I'm using is "=TabOne!B3". I've
formatted all cells in all 3 tabs to wrap. None of the cells are merged.
The cells in the 1st two tabs resize, but the cells in the "combo" tab refuse
to. What am I missing?

I also have two other issues:
1. Is there a way to hide the "0" that pre-populates the cells that contain
the formula?
2. The cells with a formula for the date pre-populate with a default
"1/0/1900" when formatted as dates. The only way I can get rid of this, is
to format the cells as "General" cells...and reformat as dates AFTER the
cells have been poplulated. Any suggestions to keep the date format, yet get
rid of the default "1/0/1900"?

Thank you

 

[Dave Peterson]

When the formulas re-evaluate, it doesn't cause excel to resize row heights.

You could use a macro that ties into the worksheet_calculate event...

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, ....

And you can use:

=if(tabone!b3="","",tabeone!b3)

This will hide those 0's when the sending cell is empty (and that funny date is
just 0 formatted as a date).

 

[Gina]

Worked! Thank you.

=IF(TabOne!B3="","",TabOne!B3)
--
David Biddulph




.

 

[Gina]

I have bad luck with macros, but the IF formula worked like a charm! Thank
you.

 

[Gina]

DARE TO DREAM. I tried the macro and it worked! Thanks.