Using Join collect from referenced sheet while ignoring criterion
=JOIN(COLLECT({Prime Student Onboarding Worksheet Rate Total}, {Prime Student Onboarding Worksheet School}, School@row, NOT(CONTAINS("SIP",{Prime Student Onboarding Worksheet Affiliate}@row))), CHAR(10))
trying to get this formula to work while ignoring cells in the rate total column that have a hyphen (this is formula driven text). I have been all through the community discussions and tried <>"-", tried making that a "" instead, tried using not(has, and many other options.
Since the Affiliate row text SIP is what determines the hyphen in the total column I am now attempting to ignore anything that has SIP in that column and I am STILL coming up with incorrect argument. everything is giving me incorrect argument.
Hope someone way smarter than me can help. :)
Best Answer
-
You are missing the range where SIP would be found.
You have
range, range, criteria, criteria
You need
range, range, criteria, range, criteria
The @cell tells the CONTAINS function to evaluate the previously established range (the one in bold above) on a cell by cell basis.
Answers
-
The only issue is the range inside of the CONTAINS function. It should simply be "@cell" (without the quotes).
NOT(CONTAINS("SIP", @cell))
-
I tried it that way and it still didn't work. I tried just @cell (incorrect argument set), @row (bad syntax), or with the reference row mentioned as above. :(
@cell (incorrect argument set)
=JOIN(COLLECT({Prime Student Onboarding Worksheet Rate Total}, AND({Prime Student Onboarding Worksheet School}, School@row, NOT(CONTAINS("SIP", @cell)))), CHAR(10))
@row (bad syntax)
=JOIN(COLLECT({Prime Student Onboarding Worksheet Rate Total}, AND({Prime Student Onboarding Worksheet School}, School@row, NOT(CONTAINS("SIP", @row)))), CHAR(10))
with reference column (incorrect argument set)
=JOIN(COLLECT({Prime Student Onboarding Worksheet Rate Total}, AND({Prime Student Onboarding Worksheet School}, School@row, NOT(HAS({Prime Student Onboarding Worksheet Affiliate} = "SIP")))), CHAR(10))
this is my original working formula:
=JOIN(COLLECT({Prime Student Onboarding Worksheet Rate Total}, {Prime Student Onboarding Worksheet School}, School@row), CHAR(10))
it produces this cell data
does it matter if the data "SIP" is coming from a vlookup?
-
The @cell would work, but it looks like you added an AND function in there for some reason. If you remove that along with the associated set of parenthesis and have it follow the appropriate syntax for the COLLECT function (range, range, criteria, range, criteria), it should work.
-
this?
=JOIN(COLLECT({Prime Student Onboarding Worksheet Rate Total}, {Prime Student Onboarding Worksheet School}, School@row, NOT(CONTAINS("SIP", @cell))), CHAR(10))
I am now getting an invalid operation error.
Also, can you help me understand to what Cell the "@cell" is pointing? how does it know what column to look in if the column isn't one of the options in the formula?
-
You are missing the range where SIP would be found.
You have
range, range, criteria, criteria
You need
range, range, criteria, range, criteria
The @cell tells the CONTAINS function to evaluate the previously established range (the one in bold above) on a cell by cell basis.
-
WOW! ok. that was way easier than I was trying to do. I tried about a million things and was probably just a little bit off each time. 😝 Thank you! it is fixed.
-
UGH! Now I tried to use the same formula to gather the same criteria data off another column and i get incorrect argument again! Bolded is the ONLY thing I changed (new reference column from same referenced sheet as before)
=JOIN(COLLECT({Prime Student Onboarding Worksheet Student}, {Prime Student Onboarding Worksheet School}, School@row, {Prime Student Onboarding Worksheet Rate Total}, NOT(CONTAINS("-", @cell))), CHAR(10))
I did change up the location for the "not(contains" because apparently not ALL of the "-" corresponded to SIP after all. but it works in the original formula
=JOIN(COLLECT({Prime Student Onboarding Worksheet Rate Total}, {Prime Student Onboarding Worksheet School}, School@row, {Prime Student Onboarding Worksheet Rate Total}, NOT(CONTAINS("-", @cell))), CHAR(10))
so now I just need to pull the student names.
-
Make sure all ranges are the same size and shape. If you are referencing individual columns, make sure you are clicking on the column header when creating the cross sheet reference. Also give it a little time to load. Sometimes the sheet doesn't fully load in before it becomes visible in the popup window. Once it does fully load, the range will revert to the "home cell" (top left cell in the sheet) and you have to re-select your range.
-
ok. you were right, but that took a LONG time to load. 🙄
I just refreshed it this morning and it was there, so sometime during the night it finally loaded. Thanks!😃
-
I've never had it take more than a minute or two to load really large / complex sheets. I would suggest trying it again a few times to see if it is still that slow. I fit is, you may want to reach out to support.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!