Invalid Data Type on an IF function with Index Collect
Hi All,
I am trying to nest and index collect into an IF but I keep on getting Invalid Data Type.
=IF(INDEX(COLLECT({Tracker - Range 5}, {Tracker - Range 2}, [Project Name]@row, {Tracker - Range 5}, "LATE"), 1), "LATE", IF(INDEX(COLLECT({Tracker - Range 5}, {Tracker - Range 2}, [Project Name]@row, {Tracker - Range 5}, "On Time"), 1)), "On Time")
Any insight is greatly appreciated!
Best Answer
-
Cory Page ✭✭✭✭✭
@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")
Hope this is useful.
Answers
-
MegBurns23 ✭✭
What I am trying to get to here is discovering if there is any Late lines within a project. Open to any ideas or suggestions. Thanks!
-
Cory Page ✭✭✭✭✭
Would you be able to provide some screenshots or examples of the data it doesn't have to be real data just examples are fine.
Thanks,
-
MegBurns23 ✭✭
@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.
-
MichaelTCA ✭✭✭✭✭
Hello@MegBurns23
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.
-
MegBurns23 ✭✭
@MichaelTCAThis worked for the first line, when I carried down the formula it does not show On Time for the projects that do not have a late value.
-
MichaelTCA ✭✭✭✭✭
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.
-
MegBurns23 ✭✭
@MichaelTCAGetting another incorrect argument set with the new formula. I made helper columns with late and on time.
=IF(INDEX({Tracker - Range 6}, MATCH([Project Name]@row, { Tracker - Range 2}, 0)) = [Late Helper]@row, [Late Helper]@row, INDEX({Tracker - Range 6}, MATCH([Project Name]@row, { Tracker - Range 2}, 0)) = [On Time Helper]@row, [On Time Helper]@row)
Are we still referencing too much of the same data?
-
MichaelTCA ✭✭✭✭✭
Did you copy and paste the function?
{ T使人痛苦的人,跑ge 2} This range has a space in the front, not sure if it's supposed to or not. There's no space in the original function.
-
Cory Page ✭✭✭✭✭
@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")
Hope this is useful.
-
MegBurns23 ✭✭
@Cory Pageawesome, this worked! And totally for keeping it simple. Thank you so much!
-
Cory Page ✭✭✭✭✭
Glad it worked for you, i just love the countifs and sumifs formula's they are so useful especially tied to an if statement.
Happy Tuesday,
Help Article Resources
Categories
Hi @BristolCVN<\/a> <\/p> I hope you're well and safe!<\/p> You must add\/change the Rule #1 name.<\/p> Did that work\/help? <\/p> I hope that helps!<\/p> Be safe, and have a fantastic week!<\/p> Best,<\/p> Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p> ✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/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":[{"tagID":443,"urlcode":"dynamic-view","name":"Dynamic View"}]},{"discussionID":108759,"type":"question","name":"Help with formula","excerpt":"I am wanting to count the number of drop down items selected in a cell, but I only want it to count in the cell for a specific row. This is the sheet that i want to count the number of dropdown items from. This is the sheet that i want the formula written in to return an output. Currently I have =IF(HAS({centers},…","snippet":"I am wanting to count the number of drop down items selected in a cell, but I only want it to count in the cell for a specific row. This is the sheet that i want to count the…","categoryID":322,"dateInserted":"2023-08-09T20:13:45+00:00","dateUpdated":null,"dateLastComment":"2023-08-10T17:17:45+00:00","insertUserID":161673,"insertUser":{"userID":161673,"name":"Lauren Hughes","url":"https:\/\/community.smartsheet.com\/profile\/Lauren%20Hughes","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!WSHqXkuSMHM!nddov_PnFbs!Fw3jLOwOmG0","dateLastActive":"2023-08-10T17:37:14+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-10T17:25:14+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":27,"score":null,"hot":3383301690,"url":"https:\/\/community.smartsheet.com\/discussion\/108759\/help-with-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108759\/help-with-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108759,"commentID":390080,"name":"Re: Help with formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390080#Comment_390080","dateInserted":"2023-08-10T17:17:45+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-10T17:25:14+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\/CM7LO30OUR2Q\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-10T17:16:38+00:00","dateAnswered":"2023-08-09T21:23:23+00:00","acceptedAnswers":[{"commentID":389908,"body":" Try this:<\/p> =COUNTM(COLLECT({July}, {Centers}, HAS(@cell, Center@row)))<\/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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">