Forum Discussion

LauraJackson's avatar
LauraJackson
Copper Contributor
Jun 12, 2023

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!

Excel
Formulas and Functions
    • LauraJackson's avatar
      LauraJackson
      Copper Contributor
      Jun 12, 2023
      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?
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Jun 12, 2023

        LauraJackson 

        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

Resources