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 H2 equals 'Ownership Company', and Q2 is blank, then populate Q2 from AI2.
If H2 equals 'Management Company', and Q2 is blank, then populate Q2 from AB2.

Any suggestions on how to create this as a formula for column Q2?

Thank you!

3 Replies
best response confirmed by LauraJackson (Copper Contributor)
Solution

# Re: Formula assistance

In Q2:

=IFS(H2="Franchisor", AQ2, H2="Ownership Company", AI2, H2="Management Company", AB2, TRUE, "")

# Re: Formula assistance

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?

# Re: Formula assistance

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``````