Distinct/Index/Match with additioanl condition of having a box checked

Sarah_lee123

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!

Brent Wilson

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.

Sarah_lee123

Thanks for this,

Unfortunately it did not work as planned.

Here is what I put in the cell

Here is what was returned

Sarah_lee123

@Brent Wilson

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

Genevieve P.

Hi @Sarah_lee123

You can use a COLLECT function as a sort of filter within a formula like this to exclude any rows that are un-checked from your Distinct Range. See: COLLECT Function

Try this:

=INDEX(DISTINCT(COLLECT({EMPLOYEE}, {Current Column}, 1)), Number@row)

Cheers,

Genevieve

Sarah_lee123

@Genevieve P.

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

Thanks,

Genevieve P.

Hi @Sarah_lee123

Do either of your two referenced columns contain a formula error message?

{EMPLOYEE} or {CURRENT EMPLOYEE}?

Sarah_lee123

@Genevieve P.

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

Genevieve P.

Hi @Sarah_lee123

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 cross-sheet references are looking at the correct columns?

Sarah_lee123

@Genevieve P.

Worked great, thanks!