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

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