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

SNickNBCUniUSH

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.

Lucas Rayala

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

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

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

@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!

Parkwide Migration.png

WiFi Tracker.png