Forum Discussion
Solved
Formula assistance
Hi everyone, I am looking for assistance with creating a formula. I am still learning. Here is what I am looking to do: If H2 equals 'Franchisor', and Q2 is blank, then populate Q2 from AQ2. If...
In Q2:
=IFS(H2="Franchisor", AQ2, H2="Ownership Company", AI2, H2="Management Company", AB2, TRUE, "")
Hi Hans, this is very close!
When I have a value in Q2, I want it to keep that value. I noticed with this formula it is taking what is in other fields to populate Q2 with even if Q2 already has a value. Any thoughts?
When I have a value in Q2, I want it to keep that value. I noticed with this formula it is taking what is in other fields to populate Q2 with even if Q2 already has a value. Any thoughts?
A cell cannot contain both a fixed value and a formula. You'll have to use VBA instead.
The following assumes that the value of H2 is entered or selected by the user.
Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the code listed below into the worksheet module.
Switch back to Excel.
Save the workbook as a macro-enabled workbook (*.xlsm).
Make sure that you allow macros when you open the workbook.
Sample workbook attached.
Private Sub Worksheet_Change(ByVal Target As Range)
Const H_Range = "H2:H16" ' change as needed
Dim rng As Range
Dim r As Long
If Not Intersect(Range(H_Range), Target) Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each rng In Intersect(Range(H_Range), Target)
r = rng.Row
If Range("Q" & r).Value = "" Then
Select Case rng.Value
Case "Franchisor"
Range("Q" & r).Value = Range("AQ" & r).Value
Case "Ownership Company"
Range("Q" & r).Value = Range("AI" & r).Value
Case "Management Company"
Range("Q" & r).Value = Range("AB" & r).Value
Case Else
' ignore
End Select
Else
' Leave cell in column Q alone
End If
Next rng
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub