Invalid Data Type on an IF function with Index Collect

Options

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!

Tags:

Best Answer

  • Cory Page
    Cory Page ✭✭✭✭✭
    edited 07/25/23 Answer ✓
    Options

    @MegBurns23 Thanks 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
    MegBurns23 ✭✭✭
    Options

    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
    Cory Page ✭✭✭✭✭
    Options

    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
    MegBurns23 ✭✭✭
    edited 07/25/23
    Options

    @Cory Page this is the direction I am going in. I want to be 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
    MichaelTCA ✭✭✭✭✭✭
    Options

    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
    MegBurns23 ✭✭✭
    Options

    @MichaelTCA This 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
    MichaelTCA ✭✭✭✭✭✭
    edited 07/25/23
    Options

    @MegBurns23

    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
    MegBurns23 ✭✭✭
    Options

    @MichaelTCA Getting 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
    MichaelTCA ✭✭✭✭✭✭
    Options

    @MegBurns23

    Did you copy and paste the function?

    { Tracker - Range 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
    Cory Page ✭✭✭✭✭
    edited 07/25/23 Answer ✓
    Options

    @MegBurns23 Thanks 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
    MegBurns23 ✭✭✭
    Options

    @Cory Page awesome, this worked! And totally for keeping it simple. Thank you so much!

  • Cory Page
    Cory Page ✭✭✭✭✭
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!