Forum Discussion
LauraJackson
Jun 12, 2023Copper Contributor
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...
- Jun 12, 2023
In Q2:
=IFS(H2="Franchisor", AQ2, H2="Ownership Company", AI2, H2="Management Company", AB2, TRUE, "")
HansVogelaar
Jun 12, 2023MVP
In Q2:
=IFS(H2="Franchisor", AQ2, H2="Ownership Company", AI2, H2="Management Company", AB2, TRUE, "")
LauraJackson
Jun 12, 2023Copper Contributor
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?
- HansVogelaarJun 12, 2023MVP
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