Conditional Cross Reference Help

jgenove

@Paul Newcome need your help! I need a formula that when any value in a column from another sheet is entered, it will then copy the text in another column in that same sheet to the sheet I am creating.

Paul Newcome

Are you able to provide some screenshots for context?

jgenove

So I would like a new sheet to populate the text in any cell in the"FDH ID" column when any value is entered in the cell on the same row in the "Locates Called" column.

Screenshot 2025-05-22 133247.png

The goal is to have over 50 other sheets to link so that I can create notifications from one sheet rather than managing notification in all 50+ sheets.

Paul Newcome

Will the data in the [Locates Called] column ever change on a row once it is entered?

jgenove

No there should not be any reason for it to change

Paul Newcome

Is the ID unique across sheets as well, or can you have the same ID on multiple sheets?

jgenove

The IDs are unique on the sheet being referenced and will not be on the 50 other sheets I eventually would like to ultimately link/reference.

Paul Newcome

Ok. In that case, I would suggest adding a helper (flag type) column to each of the 50 sheets to check if a copy already exists on the "Notification Sheet".

=IF(COUNTIFS({Notification Sheet ID Column}, @cell = [FDH ID]@row) > 0, 1)

Then you would set up an automation to run on a regular basis (daily for a single automation or multiple daily automations to run as frequently as every hour). You would have 2 conditions. One where the count column isn't blank and another where the flag column isn't checked.

jgenove

Thanks @Paul Newcome. There was fault in my logic and avoid potential issues so I need to go a different direction. I can make this formula work on the same sheet:

=IF(NOT(ISBLANK([Test 1]@row)), [Test 2]@row)

But when I try to reference another sheet it comes back as unparseable:

=IF(NOT(ISBLANK({Project Metrics by FDH - Arlington Range 1}@row)), {Project Metrics by FDH - Arlington Range 2}@row)

Paul Newcome

Ok. What is the new logic?

jgenove

When a cell in {Project Metrics by FDH - Arlington Range 1} is not blank then return the content of the cell in the same row in {Project Metrics by FDH - Arlington Range 2}.

This way any input in range 1 would trigger returning the content in range 2.

Paul Newcome

There's no easy way to set this up to scale well. For a single sheet, ok. For 50 sheets… technically it can be done, but that would be a mess and a pain all at the same time. Does the data need to be dynamic as in it will trigger additional automations if {Range 2} changes, or do you just need to send an automation one time on {Range 2} when {Range 1} is no longer blank?

jgenove

Static. Once an input on range 1 is made there should not be a reason to change unless it was a mistake. In that case multiple notifications would be ok. Range 2 will always be the same.

Paul Newcome

In that case I still stand by my original suggestion of copying the row over. Setting up formulas to pull from 50 sheets will be an absolute nightmare and will very quickly run into various limitations.

jgenove

What formula would you suggest to copy the cells in a column from another sheet?

Paul Newcome

You would first need to insert a text/number column (called "Number" in this example) and pre-fill it with the numbers 1 through however many you think you may need plus a little buffer. So if you think you are going to have 100 rows in the source sheet, I would recommend going to at least 125.

Then your formula would look like this:

=IFERROR(INDEX({Source Sheet Column To Pull Over}, Number@row), "")

A few problems with this…

You won't be able to apply column formulas because each sheet requires its own set of {Cross Sheet References}. This means a much more tedious build.

You could potentially run into a {Cross Sheet Reference} limitation. You are not allowed to have more than 100 unique {Cross Sheet References} within a sheet. If you are linking 50 sheets, that's exactly the limit.

That many cross sheet reference formulas is going to slow your sheet way down. It may even become unusable.

Depending on how many rows you need per sheet, you may run into issues with too many cells/rows as there is a limit there as well.

Automations cannot be triggered off of cells containing cells links or cross sheet references. You would have to set your trigger to run daily and then build in conditions. This makes it very challenging to send only one notification because we can't access how many times each row has already triggered an automation.