Populate different cell than formula

[Elston]

So in cell D5 i have my rent amount. In cell B5 i have the tenant name. If there is no tenant then in cell B5 i type VACANT.
is there a formula that i can type in cell A5 that will automatically populate the word "VACANT" in cell B5 if there is no rent entered in cell D5?

 

[Joe4]

A few things:
- Formulas can only return values to the cells that they reside in. They cannot return values to other cells.
- A cell can either have a hard-coded value or a formula in it, but never both at the same time. It is a "one-or-the-other" proposition.

That being said, what you want to do probably can be done using VBA, if that is a viable option for you.

 

[mumps]

If you place this formula in B5: =IF(D5="","VACANT","") it should work. However, as soon as you enter a tenant name in B5, the formula will be overwritten.

 

[Elston]

A VBA is an option. do you know the wording? Also i am not very familiar with this so could you also include the how to details? Thank you in advance

 

[Joe4]

So, just to understand the flow, and how you want all of this to work, tell me if this proposal will work for you.
Assuming that column B and column I both have entries in them, if the value in column I is deleted/removed, the value in column B should change to "VACANT"?
And, you want this to happen upon the event of someone manually updating column I?
Is that all correct?

 

[Joe4]

If my previous assumptions are correct, then right-click on the sheet tab name at the bottom of your sheet, select "View Code", and paste this VBA code in the resulting VB Editor window.
It should do what you want automatically.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim isect As Range
    Dim cell As Range
    
'   See if cells being updated are in column I
    Set isect = Intersect(Target, Range("I:I"))
    
'   Loop through updated cells in column I
    If Not isect Is Nothing Then
        For Each cell In isect
            Application.EnableEvents = False
            If cell = "" Then cell.Offset(0, -7) = "VACANT"
            Application.EnableEvents = True
        Next cell
    End If
    
End Sub