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

Options
Sarah_lee123
Sarah_lee123 ✭✭
edited 07/18/22 in Formulas and Functions

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!

Tags:

Answers

  • Brent Wilson
    Brent Wilson ✭✭✭✭✭
    Options

    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

  • Sarah_lee123
    Options

    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
    Options

    @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.
    Genevieve P. Employee Admin
    Options

    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
    Options

    @Genevieve P.


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

    Thanks,

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Sarah_lee123

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

    {EMPLOYEE} or {CURRENT EMPLOYEE}?

  • Sarah_lee123
    Options

    @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.
    Genevieve P. Employee Admin
    Options

    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
    Options

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!