Auto expanding Cells and IF functions.

[Graham C1600]

Hi All,

Hopefully this is a simple fix for something that is driving me crazy.

Basically I have 2 x cells. In the first I have a data validation list where I can choose something. The second cell displays some text depending on what was chosen in the first cell with a simple =IF function.
When I am doing this the cell with the IF function will show the correct text but the cell will not resize automatically to display all of the text. I am wanting to leave all the cells a standard small size to make it look uniform but obviously when I use the drop down and get the answer in the second cell, I want the cells to auto expand and show all the text.

The cells with the data validation list will expand automatically which I suspect is because it has text already in there but because the second cell is empty until a value is chosen, it won't expand to show anything.

Can anybody assist with this ?

Thanks

 

[Kaper]

You could use VBA for that.

I'd employ hete event handler for Change event in a worksheet.

Assuming your validation list is in column A and your IF formula is in column B something like that:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns("A:A")) Is Nothing Then
  Application.Calculate
  Columns("B:B").AutoFit
End If
End Sub

insterted into worksheet code (right-click on a tab with worksheet name and select "Display Code", then copy code there and save your file as macro-enabled .xlsm format) should do.