Question

=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 · Accepted Answer

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 · Accepted Answer

@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 · Accepted Answer

@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