@MegBurns23Thanks for all the information sorry it took a bit.
Its not a collect statement but sometimes keeping something as simple as possible really makes things easy so just in case you wanted to try something a little different here is one I ended up getting to work.
Its basically counting the number of Late instances then anything greater than 0 would mean the project is late other wise its On time. Update: Sorry i didn't drop the correct formula fixed now
=IF(COUNTIFS([Late or on time]3:[Late or on time]44, ="Late", [Project Name]3:[Project Name]44, [Other sheet Primary]@row) > "0", "Late", "On Time")
@Cory Page这是我要的方向。我想成为able to mark a project LATE if any line on the other sheet is late for that project. If there are no late lines, then the project is On Time.
You could try: (Column@row is a variable because I'm not sure what the column name is)
=IF(INDEX(COLLECT({Tracker - Range 5}, {Tracker - Range 2}, [Project Name]@row, {Tracker - Range 5},Column@row), 1)="LATE", "LATE", IF(INDEX(COLLECT({Tracker - Range 5}, {Tracker - Range 2}, [Project Name]@row, {Tracker - Range 5},Column@row), 1)="On Time", "On Time")).
But you are using a lookup function that already contains criteria, so it's kind of excess syntax unless you are wanting to present a value not contained within the evaluated range.
If "LATE" is already defined as a value to be used for a criteria in the lookup function, then LATE will already be displayed and having an argument that says if the value is "LATE" then set the cell equal to "LATE" can be enough to confuse the software.
Sometimes when using the same column as a lookup and a criteria, the function can error or be considered a circular reference if you're comparing calculated variables around multiple sheets.
Are there any other values to present besides Late and On Time?
If so, you could skip the second lookup function and use "On Time" for the false statement.
You could try:
=IF(INDEX({Tracker - Range 5},MATCH([Project Name]@row,{Tracker - Range 2},0))="LATE","LATE",INDEX({Tracker - Range 5},MATCH([Project Name]@row,{Tracker - Range 2},0))="On Time", "On Time")
Using the INDEX/COLLECT function evaluates a range, but can return a single value. For example MAX(COLLECT()) will show the single maximum value out of the range. It's nice because you can use multiple criteria.
INDEX/MATCH evaluates row by row instead of a whole range.
@MegBurns23Thanks for all the information sorry it took a bit.
Its not a collect statement but sometimes keeping something as simple as possible really makes things easy so just in case you wanted to try something a little different here is one I ended up getting to work.
Its basically counting the number of Late instances then anything greater than 0 would mean the project is late other wise its On time. Update: Sorry i didn't drop the correct formula fixed now
=IF(COUNTIFS([Late or on time]3:[Late or on time]44, ="Late", [Project Name]3:[Project Name]44, [Other sheet Primary]@row) > "0", "Late", "On Time")
Is the column you are inserting the formula into formatted as a date column?<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108786,"type":"question","name":"Can't Save Field Logic Updates in Dynamic View","excerpt":"Hi all, I've seen a few similar posts regarding this, but they were from a few years back so not sure if anything has changed. I want to add Field Logic to a field in our form. It asks the user to select a Category and to deter using Issues\/Miscellaneous, I have added a dropdown column in the sheet for the user to confirm…","snippet":"Hi all, I've seen a few similar posts regarding this, but they were from a few years back so not sure if anything has changed. I want to add Field Logic to a field in our form. It…","categoryID":322,"dateInserted":"2023-08-10T14:14:23+00:00","dateUpdated":null,"dateLastComment":"2023-08-10T16:42:12+00:00","insertUserID":158227,"insertUser":{"userID":158227,"name":"BristolCVN","url":"https:\/\/community.smartsheet.com\/profile\/BristolCVN","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-10T15:49:46+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-10T18:07:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":19,"score":null,"hot":3383364395,"url":"https:\/\/community.smartsheet.com\/discussion\/108786\/cant-save-field-logic-updates-in-dynamic-view","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108786\/cant-save-field-logic-updates-in-dynamic-view","format":"Rich","tagIDs":[443],"lastPost":{"discussionID":108786,"commentID":390063,"name":"Re: Can't Save Field Logic Updates in Dynamic View","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390063#Comment_390063","dateInserted":"2023-08-10T16:42:12+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-10T18:07:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/8C0H47JEM9MT\/fieldlogic.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"fieldlogic.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-10T15:49:43+00:00","dateAnswered":"2023-08-10T14:51:23+00:00","acceptedAnswers":[{"commentID":390035,"body":"