Trouble referencing values in a "CONCATENATE" cell

ThermoKate

Hello, I am having issues trying to reference the values in a CONCATENATE formula. I am trying to pull a "Yes" or "No" value based on the product numbers provided in the CONCATENATE cell.

The CONCATENATE cell is the combination of product numbers added just for TODAY only (old lines do not show a value unless it is today). I'd like for the formula to reference this cell and match the new product numbers with the old product number (I.E. If an old request has the same product number then I'd like for the cell to show as a "Yes" value.)

(The values show as 123546. 5647. 1727230. AF7268. RT91820)

I tried an IF HAS formula but it is returns a "No" for every line

I tried a helper column on another sheet but it seems like Smartsheet cannot read the multiple values as individual. Any tips or tricks?

jmyzk_cloudsmart_jp

Hi @ThermoKate

I would use the range created by the COLLECT function for the search_range of the HAS(range, criterion) function instead of the CONCATENATED or JOINed cell, as the concatenated cell is a text.

For example, the demo sheet below checks whether an old product number, 1727330, in the Sheet Summary filed is in the CONCATENATE cell.

The formula is;

=IF(HAS(COLLECT([Product Number]:[Product Number], [Date Opened]:[Date Opened], TODAY(), [Trending Report]:[Trending Report], "New"), [Old Product Number]#), "Yes", "No")

https://app.smartsheet.com/b/publish?EQBCT=5e28d6c546ba4e2aae2e7e2e14003a17

bisaacs

Hey @ThermoKate,

Are you using a JOIN function or just using a "+" for your concatenate formula? Also, are you trying to match the full product number or just a portion? If you're trying to check if the match fully, I feel like you could use a more straightforward formula to check the new/old formula:

=IF((%CONCATENATE FORMULA%) = [New product number]@row, "YES", "NO")

Could you maybe provide a screenshot so we can see how its formatted?

ThermoKate

The formula has a JOIN and COLLECT function

=IF([Date Opened (Column1)]@row = TODAY(), (JOIN(COLLECT([Product Number]:[Product Number], [Date Opened (Column 1)]:[Date Opened (Column 1)], =TODAY(), [Trending Report]:[Trending Report], ="New"), ". ")), "")

jmyzk_cloudsmart_jp

Hi @ThermoKate

I would use the range created by the COLLECT function for the search_range of the HAS(range, criterion) function instead of the CONCATENATED or JOINed cell, as the concatenated cell is a text.

For example, the demo sheet below checks whether an old product number, 1727330, in the Sheet Summary filed is in the CONCATENATE cell.

The formula is;

=IF(HAS(COLLECT([Product Number]:[Product Number], [Date Opened]:[Date Opened], TODAY(), [Trending Report]:[Trending Report], "New"), [Old Product Number]#), "Yes", "No")

https://app.smartsheet.com/b/publish?EQBCT=5e28d6c546ba4e2aae2e7e2e14003a17

ThermoKate

That worked! Thank you!!

jmyzk_cloudsmart_jp

@ThermoKate

You can also use the CONTAINS function to use the CONCATENATE cells themselves.

=IF(CONTAINS([Old Product Number]#, CONCATENATE:CONCATENATE), "Yes", "No")