Join + Distinct formula in Global Updates

aecross

Hi -

Trying to update 1 specific cell across all my sheets in Control Center with the below formula in global updates, but it does not register the last portion of the range. I get the error pop up "The formula you entered contains 2 or more different row references. If you're attempting to replace this formula pattern in every row, it will not match since this feature is not supported." I'm not trying to replace every row, but i need every row accounted for in this formula. How can i rewrite the formula to include every row?

=JOIN(DISTINCT([Architecture Team]2:[Architecture Team]1667), "; ")

I tried with the absolute value $ and it still didn't work.

Georgie

Hi @aecross,

To reference the entire “Architecture Team” column, you’ll just need to remove the row numbers from your formula, so instead of including “2” and “1667”, your formula will look like this:

• =JOIN(DISTINCT([Architecture Team]:[Architecture Team]), "; ")

If the cells in your “Architecture Team” column contain multiple data types (for example, if some cells contain numbers, but some cells contain text strings), then you’ll see an #INVALID DATA TYPE error. The DISTINCT function requires that all values are of the same data type.

Does the formula above work for you?

Georgie

Georgie

Hi @aecross,

To reference the entire “Architecture Team” column, you’ll just need to remove the row numbers from your formula, so instead of including “2” and “1667”, your formula will look like this:

• =JOIN(DISTINCT([Architecture Team]:[Architecture Team]), "; ")

If the cells in your “Architecture Team” column contain multiple data types (for example, if some cells contain numbers, but some cells contain text strings), then you’ll see an #INVALID DATA TYPE error. The DISTINCT function requires that all values are of the same data type.

Does the formula above work for you?

Georgie

aecross

Hi @Georgie - yes, that did the trick. Thank you!!