Dropdown box populating another cell with 0

[AMorgan]

I have a dropdown list in a cell that populates a cell in another spreadsheet. The formula is =worksheet!A20
however, if nothing is selected in the dropdown list, the cell in the other spreadsheet shows 0.
How do I stop the cell from populating with 0? I want the cell left blank if nothing is selected from the dropdown box.

 

[syntaxed]

How about: =IF(ISBLANK(worksheet!A20),"",worksheet!A20)

 

[AMorgan]

Works perfectly - thanks!
I have another formula:
=TEXT(worksheet!C20,"dd mmmmmm yyyy")&" "&"to"&" "&TEXT(worksheet!F20,"dd mmmmmm yyyy")
that is getting information from another cell in another spreadsheet. It is showing a date range of 00 January 1900 to 00 January 1900 if the cell where the information is coming from is left blank. How do I get the cell to remain blank if no date is put in or to populate with a date if it is put in. Hope that makes sense!

 

[syntaxed]

Maybe: =IF(AND(ISNUMBER(worksheet!C20),ISNUMBER(worksheet!F20)),TEXT(worksheet!C20,"dd mmmmmm yyyy")&" "&"to"&" "&TEXT(worksheet!F20,"dd mmmmmm yyyy"),"")

 

[AMorgan]

Worked again! Thank you!
One more question: The formulas you have provided populate cells in a number of pro-forma letters in Excel. Cells are populated from a main worksheet. There is room for 8 lines of data in each letter using the above formulas, however, if all of these lines aren't needed, then it leaves a big gap in the letter. Is there some way of removing the lines automatically if they are not populated and does this need to be incorporated in the above forumulas?

 

[syntaxed]

Yes those lines can be hidden automatically but it can't be done through formulas.

A macro could be written that would be triggered whenever the cells are populated. If all the cells in the specified range were blank, then it could hide those rows automatically for you, thereby removing the gap from the letter.

 

[AMorgan]

I haven't written a macro for anything like that before. Is it easy to do?

 

[syntaxed]

You could right-click the sheet tab that contains your formulas, left click "View Code" and paste something along these lines in the window that appears:

Private Sub worksheet_calculate()
Set myRange = Range("a1:h20") 'change this
Application.EnableEvents = False
For myRow = myRange.Row To myRange(myRange.Rows.Count, 1).Row
    With Range(Cells(myRow, myRange.Column).Address & ":" & Cells(myRow, myRange.Columns.Count + myRange.Column - 1).Address)
        If Application.CountBlank(.Cells) = .Cells.Count Then
            Cells(myRow, myRange.Column).EntireRow.Hidden = True
        Else
            Cells(myRow, myRange.Column).EntireRow.Hidden = False
        End If
    End With
Next
Application.EnableEvents = True
End Sub

You may want to try this on a copy of your workbook first. And make sure macros are enabled. Also, the range "a1:h20" given above should be changed to the range that contains your formulas that you want to hide if it is blank.

 

[AMorgan]

You are brilliant!!! Thank you!

 

[AMorgan]

The code worked for the first letter that I did, however, in subsequent letters (set up on individual tabs at the bottom of the spreadsheet) it didn't, because the fields that I want to delete are different. That is, in the first letter it is A21:C21 however in the next letter it's A21:B32. Is there some way of having different code in each individual worksheet (letter)?