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
Need more help? 👀  Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋  Global Discussions

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}?
Need more help? 👀  Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋  Global Discussions

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?
Need more help? 👀  Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋  Global Discussions

Worked great, thanks!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 406 Global Discussions
 219 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!