Invalid Data Type on an IF function with Index Collect

MegBurns23

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!

Cory Page

@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

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 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

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

@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

@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

@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

@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

@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

@Cory Page awesome, 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,