#INVALID OPERATION

Eden

I'm trying to use a mix of IF, index,match, and max, but it keeps prompting me that INVALID OPERATION. I want to find this employee in 2024 leave master data for Admin ONLY this sheet in the latest date of the Annual Leave Left Days, but this formula shows #INVALID OPERATION. May I can ask if you can tell me how to set the formula? Thank you!

And could you please help me find out what's wrong with what's wrong with the formulas? Thanks!

=IF({2024 leave master data for Admin ONLY Range 5} = Employee@row, INDEX({2024 leave master data for Admin ONLY Range 2}, MATCH(MAX({2024 leave master data for Admin ONLY Range 3}), {2024 leave master data for Admin ONLY Range 3}, 0)), " ")

Best regards

Eden

jmyzk_cloudsmart_jp

Hi @Eden

I was not so sure what you meant by {Range 5}, {Range 2}, and {Range 3}, but what I am sure is in your IF statement in your formula, "IF({2024 leave master data for Admin ONLY Range 5} = Employee@row", is comparing a range with a cell value, which should cause an #INVALID OPERATION error. (In the MATCH part of your formula, you will get the row number of the MAX value of the latest date column, regardless of the value of the Employee@row. So, I think you used the IF statement to limit the MATCH range, but as I wrote above, you can not compare a range with a cell value.)

I interpreted your assignment as taking the remaining annual leave days per employee. I then interpreted it as taking from the number of days remaining for a particular employee values on the admin sheet, the one with the latest value of the employee's row.

The formula of Anual Leave Left Days in the Leave Left for Employee sheet is as follows;

[Annual Leave Left Days] =INDEX(COLLECT({Annual Leave Left Days}, {Employee}, Employee@row), COUNT(COLLECT({Created}, {Employee}, Employee@row)))

In English, the formula means to first get the employee's Annual Leave Left Days and then give me the last or latest one, which I think is a more natural way of thinking.

For the first part of the formula, as the range for the INDEX function, I used the COLLECT function to get a range of the {Annual Leave Left Days} in the Admin ONLY sheet on the right of the image below whose {Employee} range is the same as Employee@row in the look-up sheet on the left of the image below. For example, for Employee app@cloudsmart.jp, the COLLECT function will return the range of {15,14,11).

Then, in the latter part of the formula, as the row index of the INDEX function, I used the COUNT function to get the number of entries or rows whose Employee values on the Admin Sheet are the same as the Employee@row in the look-up sheet. For example, for Employee app@cloudsmart.jp, there are three rows or entries.

An alternative formula that uses the MAX function for the Created column is as follows;

[Annual Leave Left Days 2] =INDEX({Annual Leave Left Days}, MATCH(MAX(COLLECT({Created}, {Employee}, Employee@row)), {Created}, 0))

In English, the formula means from all the Annual Leave Left Days column values, give me the one whose row number is the same as the Created date of the employee and the largest.

This formula uses the entire {Annual Leave Left Days} range of the Admin ONLY sheet as the range for the INDEX function and then uses the MAX and COLLECT functions for the MATCH function's search value. For example, for the Employee app@cloudsmart.jp, the MATCH function returns 7 of the 7th row in the Admin ONLY sheet.

I first came up with the first formula, but the second one looks more straightforward.

https://app.smartsheet.com/b/publish?EQBCT=a5c74ff54d5448cb8a81911cdc33f9c0

Kerry St. Thomas

Your MAX of Range 3 appears to be the culprit - Multiple criteria Index/Match is… well… problematic in Smartsheet. So as an alternate, I suggest using COLLECT as part of the solution:

=INDEX({ Admin Leave Hours }, MATCH(MAX(COLLECT( {Admin Entry Dates}, {Admin Employee Names}, Employee@row)), {Admin Entry Dates}), 0)

There is a potential issue though: If there's more than one entry on the same day for the same employee, the formula may not work as expected. Double-check your Date column to ensure that the likelihood of that is minimized (use the Created Date system column that includes time and converting it to a number on the Admin sheet and use that as the MAX measure column instead of the Dates).


If this answer resolves your question, please help the Community by marking it as an accepted answer.  I'd also be grateful for your response - "Insightful", "Vote Up", and/or "Awesome" as appropriate. Thanks!

jmyzk_cloudsmart_jp

Hi @Eden

I was not so sure what you meant by {Range 5}, {Range 2}, and {Range 3}, but what I am sure is in your IF statement in your formula, "IF({2024 leave master data for Admin ONLY Range 5} = Employee@row", is comparing a range with a cell value, which should cause an #INVALID OPERATION error. (In the MATCH part of your formula, you will get the row number of the MAX value of the latest date column, regardless of the value of the Employee@row. So, I think you used the IF statement to limit the MATCH range, but as I wrote above, you can not compare a range with a cell value.)

I interpreted your assignment as taking the remaining annual leave days per employee. I then interpreted it as taking from the number of days remaining for a particular employee values on the admin sheet, the one with the latest value of the employee's row.

The formula of Anual Leave Left Days in the Leave Left for Employee sheet is as follows;

[Annual Leave Left Days] =INDEX(COLLECT({Annual Leave Left Days}, {Employee}, Employee@row), COUNT(COLLECT({Created}, {Employee}, Employee@row)))

In English, the formula means to first get the employee's Annual Leave Left Days and then give me the last or latest one, which I think is a more natural way of thinking.

For the first part of the formula, as the range for the INDEX function, I used the COLLECT function to get a range of the {Annual Leave Left Days} in the Admin ONLY sheet on the right of the image below whose {Employee} range is the same as Employee@row in the look-up sheet on the left of the image below. For example, for Employee app@cloudsmart.jp, the COLLECT function will return the range of {15,14,11).

Then, in the latter part of the formula, as the row index of the INDEX function, I used the COUNT function to get the number of entries or rows whose Employee values on the Admin Sheet are the same as the Employee@row in the look-up sheet. For example, for Employee app@cloudsmart.jp, there are three rows or entries.

An alternative formula that uses the MAX function for the Created column is as follows;

[Annual Leave Left Days 2] =INDEX({Annual Leave Left Days}, MATCH(MAX(COLLECT({Created}, {Employee}, Employee@row)), {Created}, 0))

In English, the formula means from all the Annual Leave Left Days column values, give me the one whose row number is the same as the Created date of the employee and the largest.

This formula uses the entire {Annual Leave Left Days} range of the Admin ONLY sheet as the range for the INDEX function and then uses the MAX and COLLECT functions for the MATCH function's search value. For example, for the Employee app@cloudsmart.jp, the MATCH function returns 7 of the 7th row in the Admin ONLY sheet.

I first came up with the first formula, but the second one looks more straightforward.

https://app.smartsheet.com/b/publish?EQBCT=a5c74ff54d5448cb8a81911cdc33f9c0

Eden

Hi @jmyzk_cloudsmart_jp

Thank you very much for your help, the formula works fine!👍️

Best regards

Eden

Eden

Hi @Kerry St. Thomas

Thank you for your Help!!👍️

Best regards

Eden