what is wrong with my formula join collect?

Deonizia Egan

=JOIN(COLLECT({Rollup Tracking: Preceptor Activity Month}, {Rollup Tracking: Preceptor Activity Preceptor Name}, [Preceptor Name]@row, {Rollup Tracking: Preceptor Activity QTR}, Qtr@row, {Rollup Tracking: Preceptor Activity Year}, Year@row), CHAR(10))

I cannot see why this doesn't work…i have similar formulas on other sheets that work just fine with more complicated criteria. i've double checked the formatting and reference columns. Everything colors up as it should, but it returns nothing. no error, just nothing.

this truncated one works, and pulls the months as expected, but i need the added criteria.

=JOIN(COLLECT({Rollup Tracking: Preceptor Activity Month}, {Rollup Tracking: Preceptor Activity Preceptor Name}, [Preceptor Name]@row), CHAR(10))

these simple things keep getting the better of me. :(

one one below is the shorter formula, row two is the longer formula.

Kelly Moore

Hey @Deonizia Egan

In your source sheet check your Quarter and Year columns. Are they both showing as numeric values (right justified) vs textstrings (left justified)? As a test, if you were to remove either Quarter criteria or Year criteria from the formula, does the formula work?

Please let me know what you find

Kelly

Kelly Moore

@Deonizia Egan

The dropdown column shouldn’t be a problem. The issue are the quotes you placed around the numbers. Quotes convert characters to a text string. You want the characters to remain as numbers. Remove the quotes from formula

Kelly

Kelly Moore

@Deonizia Egan
Glad your formula is working.

In case you are unaware, when trying to match data to a reference, in many Functions there is a requirement that both the reference and the matching data be either both text strings or both numeric. That is why I asked you if the Quarter column on your source sheet was right justified or left justified. Right justified data (assuming you didn’t manually adjust the justification) indicates that smartsheet is treating the data as numeric, which I could see from your screenshot was how the destination sheet was being treated. (I never manually justify columns in order to tell at a glance how smartsheet is treating the data).

This is a good tidbit to know as you troubleshoot your formulas.

Cheers, Kelly

Kelly Moore

Hey @Deonizia Egan

In your source sheet check your Quarter and Year columns. Are they both showing as numeric values (right justified) vs textstrings (left justified)? As a test, if you were to remove either Quarter criteria or Year criteria from the formula, does the formula work?

Please let me know what you find

Kelly

Deonizia Egan

Hi Kelly,

Yes, it works when I remove the quarter, but not when I remove the year. So it is the quarter column that is the problem?

this is the quarter formula on the reference sheet:

=IF(MONTH([Current Activity Start Date]@row) < 4, "1", IF(MONTH([Current Activity Start Date]@row) < 7, "2", IF(MONTH([Current Activity Start Date]@row) < 10, "3", "4")))

Deonizia Egan

I just realized that it was set up as a drop down column! I didn't set up the original sheet, so I didn't think to look.

Thank you so much!

Kelly Moore

@Deonizia Egan

The dropdown column shouldn’t be a problem. The issue are the quotes you placed around the numbers. Quotes convert characters to a text string. You want the characters to remain as numbers. Remove the quotes from formula

Kelly

Deonizia Egan

yea, that didn't fix it after all, I will try removing the quotes.

AAANNDDD, that fixed it!!! :)

Kelly Moore

@Deonizia Egan
Glad your formula is working.

In case you are unaware, when trying to match data to a reference, in many Functions there is a requirement that both the reference and the matching data be either both text strings or both numeric. That is why I asked you if the Quarter column on your source sheet was right justified or left justified. Right justified data (assuming you didn’t manually adjust the justification) indicates that smartsheet is treating the data as numeric, which I could see from your screenshot was how the destination sheet was being treated. (I never manually justify columns in order to tell at a glance how smartsheet is treating the data).

This is a good tidbit to know as you troubleshoot your formulas.

Cheers, Kelly