If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Autofit Unmerged Cells (XP & Excel 2003)
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 |
#2
|
|||
|
|||
Autofit Unmerged Cells (XP & Excel 2003)
=IF(TabOne!B3="","",TabOne!B3)
-- David Biddulph "Gina" wrote in message ... 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 |
#3
|
|||
|
|||
Autofit Unmerged Cells (XP & Excel 2003)
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 wrote: 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 |
#4
|
|||
|
|||
Autofit Unmerged Cells (XP & Excel 2003)
Worked! Thank you.
"David Biddulph" wrote: =IF(TabOne!B3="","",TabOne!B3) -- David Biddulph "Gina" wrote in message ... 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 . |
#5
|
|||
|
|||
Autofit Unmerged Cells (XP & Excel 2003)
I have bad luck with macros, but the IF formula worked like a charm! Thank
you. "Dave Peterson" wrote: 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 wrote: 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 . |
#6
|
|||
|
|||
Autofit Unmerged Cells (XP & Excel 2003)
DARE TO DREAM. I tried the macro and it worked! Thanks.
"Dave Peterson" wrote: 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 wrote: 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 . |
Thread Tools | |
Display Modes | |
|
|