a blank cell result in Excel

Topic

New Reply

How do I get Excel 2007 to make the result of an if query a blank?

I know I can use if(a1=”a”,”yes”,””), but this does not produce a true blank. If in another part of the spreadsheet I do a calculation on the result cell, I get a ‘value’ error. If I check a cell with a double quote result with an ‘isblank’ query it return FALSE.

I also know I can use if(a1=”a”,”yes”,0), but I don’t want to fill the spreadsheet with unnecessary zeros and I can’t use conditional formatting to hide the zeroes as some are needed to display.

Anyone got any ideas

David

Reply | Quote

Replies

You cannot return a true blank. If you don’t want to return 0, you have to use “” and then adjust your other formulas to cater for the cell having “” in it.

Reply | Quote

I know I can use if(a1=”a”,”yes”,””), but this does not produce a true blank.

David,

Try placing the following code into the sheet module. If a change is made to cell A1 and that value is anything other than “a”, a null value will be placed into cell B1.

HTH,
Maud

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range(“A1”)) Is Nothing Then
        [b1] = IIf([a1] = “a”, “yes”, vbNullString)
    End If
End Sub

Reply | Quote