Distinct/Index/Match with additioanl condition of having a box checked
Trying to use distinct index to pull in information to display the running total cost of an employee, but I only want it to pull in "Current Employees"
Currently this formula works for ALL employees past and present.
=(INDEX(DISTINCT({EMPLOYEE}), Number@row)
I would like to add on to this formula to ONLY include employees that have the "CURRENT EMPLOYEE" box checked.
Trying to use this formula, but I see it's not correct.
=IF(INDEX(DISTINCT({EMPLOYEE}), Number@row, {CURRENT EMPLOYEE}, 1))
Is this possible?
Thanks!
Answers

I think I understand what the ask is
I think this might work
=IF({Current Employee}, (INDEX(DISTINCT({EMPLOYEE}), Number@row), )
Where Current Employee would be a link to the Employee Status..
Remember it is =IF(logical_expression, value_if_true, [ value_if_false ])
So if Current Employee = True then Your Calculation else nothing
This can get a bit dangerous as it doesn't have any "Checking" so if someone deletes an employee number it could go a bit wonky
You might want to do a VLOOKUP just to make sure that there is an EmployeeID = EmployeeID match in each table. You might be doing it in the summary.. it is unclear
Another thing might be to pull in the employee status from a Vlook and then Filter it out in the summary.
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com

Thanks for this,
Unfortunately it did not work as planned.
Here is what I put in the cell
Here is what was returned

Do you have any ideas about my above issue? I tried using the formula you suggested and it did not work as planned.
Thanks so much

You can use a COLLECT function as a sort of filter within a formula like this to exclude any rows that are unchecked from your Distinct Range. See: COLLECT Function
Try this:
=INDEX(DISTINCT(COLLECT({EMPLOYEE}, {Current Column}, 1)), Number@row)
Cheers,
Genevieve

Thanks for your response. Unfortunately, that did not work either. #INVALID DATA TYPE
Thanks,

Do either of your two referenced columns contain a formula error message?
{EMPLOYEE} or {CURRENT EMPLOYEE}?

The {{Employee}} column adds an apostrophe to the employee ID number in the source sheet. I think I need an @cell char (3) embedded in the equation.
Here is what the current equation looks like.
=INDEX(DISTINCT(COLLECT({ID}, {CURRENT EMPLOYEE}, 1)), Number@row)
How should I embed the IFERROR(SUBSTITUTE(@cell, CHAR(3), "")......
Thanks so much

My apologies for the delay!
The apostrophe shouldn't necessarily make a difference for the Distinct function. However formula errors are like dominoes  meaning that if you're referencing a column that has an error, that error will display in your other formula (even if the formula is written correctly).
If you have a formula in either the {ID} or {CURRENT EMPLOYEE} column, try wrapping the iferror around those formulas instead:
=IFERROR(Id formula, "")
Then see if that updates your current formula to work:
=INDEX(DISTINCT(COLLECT({ID}, {CURRENT EMPLOYEE}, 1)), Number@row)
If it doesn't, can you double check that the crosssheet references are looking at the correct columns?

Worked great, thanks!
Help Article Resources
Categories
Check out the Formula Handbook template!