Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Formula help needed

Copper Contributor

I need some help with a formula. I need to link one cell in a document tab to another tab and change the value of the second cell. for Example, Cell A in the master file Tab would be A1 that feeds, B1 in another Tabs cell. Then if the cell says "Palo Alto" (from the first tab) I need it to put a "2" in the B1 cell and if it says "THV-PA" then put a 0 in B1. 

1 Reply

Assuming that your master file tab is named "Master" and the second tab is named "OtherTab," follow these steps:

  1. In cell B1 of "OtherTab," enter the following formula:

=IF(Master!A1="Palo Alto", 2, IF(Master!A1="THV-PA", 0, ""))

Replace "Master" with the actual name of your master file tab.

 

Method 2: VBA Macro

If you want a more automated solution, you can use a VBA macro. Here's how you can set it up:

  1. Press Alt + F11 to open the VBA editor.
  2. In the VBA editor, go to Insert -> Module to add a new module.
  3. Copy and paste the following VBA code into the module:

Vba code is untested, please backup before your try this code.

Sub UpdateOtherTab()
    Dim masterValue As String
    Dim otherTab As Worksheet
    
    ' Replace "Master" with the actual name of your master file tab
    Set otherTab = ThisWorkbook.Sheets("OtherTab")
    
    ' Get the value from Master!A1
    masterValue = Sheets("Master").Range("A1").Value
    
    ' Update B1 in OtherTab based on the condition
    If masterValue = "Palo Alto" Then
        otherTab.Range("B1").Value = 2
    ElseIf masterValue = "THV-PA" Then
        otherTab.Range("B1").Value = 0
    End If
End Sub

4. Close the VBA editor.

Now, whenever you want to update the value in "OtherTab!B1" based on the value in "Master!A1," you can run this macro:

  1. Press Alt + F8 to open the "Macro" dialog.
  2. Select UpdateOtherTab and click "Run."

Choose the method that best fits your needs — whether you prefer a formula-driven approach or a VBA macro. The text and steps were edited with the help of AI.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.