Formula to change cell values based on another sheet's column condition plus unique identifier

Options

Hello,

I'm looking to change a single cell value in sheet 1, based on the following criteria in sheet 2:

All of column A where number = "1234" AND columns B, C, D = "Yes"

Essentially, I need the actions from columns B, C, and D to have happened before changing the cell value in sheet 1, but it needs to have happened for every instance where Column A = 1234.

Answers

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    Options

    Hi @SNickNBCUniUSH, how about this:

    =IF(COUNTIFS({Column A}, "1234", {Column B}, "Yes", {Column C}, "Yes", {Column D}, "Yes", {Unique ID}, UniqueID@row)>0, "My New Value", "")

    If Column A is your unique ID, you don't need ", {Unique ID}, UniqueID@row".

  • SNickNBCUniUSH
    Options

    Hey @Lucas Rayala thank you so much for this - the one thing I forgot to add is that there are multiple entries for Column A, so in another instance I might need the same triggers of column B, C, D, to make a trigger for "5678" in column A.


    Also, once Columns B, C, D = "Yes", I need that to be a trigger to input the numeric value from Column A from sheet 1 into Sheet 2.

    Do you know how I might be able to do that? Thanks again for the help!

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    Options

    Hi @SNickNBCUniUSH why don't you provide a screenshot of your two sheets so I can see how you are trying to set up the data? I don't know how you need to handle the variable column A numbers because I don't know how you are structuring your data. Regarding the addition of the Column A value in the new sheet, do this:


    =IF(COUNTIFS({Column A}, "1234", {Column B}, "Yes", {Column C}, "Yes", {Column D}, "Yes", {Unique ID}, UniqueID@row)>0, INDEX(COLLECT({Column A}, {Unique ID}, UniqueID@row),1),"")

  • SNickNBCUniUSH
    edited 02/28/23
    Options

    @Lucas Rayala Sure thing. I attached both sheets. In 'Parkwide Migration', there are multiple rows that contain the same building number.

    On the WiFi Tracker sheet, I'm looking to input "Yes" for the "Switch" column, but only when ALL instances of the same building number from "Parkwide Migration" have "Yes" under columns "Network Cabinet Upgrade", "Switch Upgrade Complete", and "UPT Fiber Cutover Complete".


    Thanks!




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!