The merged cell is not adding to the issue......it is the cause of the issue.

Wrap Text works fine on merged cells, but Autofit does not work.

You need VBA code to do that.

Here is code from Greg Wilson.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range

With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
Application.ScreenUpdating = True
End If
End With
End Sub


Gord Dibben MS Excel MVP

On Tue, 15 Aug 2006 09:34:02 -0700, SriGog
wrote:

Also, I forgot to add that the cell where I need to display the data is a
merged cell. Is that adding to the issue?

"SriGog" wrote:

I have a formula in a cell (destination) that derives a text field from
another Excel Sheet (Source). When I enter new text in the Source sheet, as
expected it populates in teh destination dield. However, the destination
field needs to be adjusted to display the entire contents each time the the
text lenght increases. Setting "Wrap Text" option does not resize the field
each time the length increases. I need this feature as part of my work, and
currently I do this manually. Thanks your suggestion

Gord Dibben MS Excel MVP