What formula can I use in the Main Sheet to bring in a Status based on the Reference sheet?

Options

Good morning all,

I have tried Index, Index-match, IF, IF Error formula's and I can't seem to get this cross reference to work but none do.

Here is the breakdown:

In the main sheet I want a column to auto populate 3 statuses:

Not Started: when the reference sheet task columns have not been checked off

In Progress: when the reference sheet has at least one of the task columns checked off

Completed: when the reference sheet has all task columns checked off.

The Reference Sheet has check boxes which are triggered by a few automations I have created.

Please see image.

I am looking for a formula that I can use in the Main Sheet. Thank you!

Tags:

Answers

  • Philip Robbins
    Philip Robbins ✭✭✭✭✭
    edited 07/09/24
    Options

    Hi @emoser1978,

    I think it would be easier to first calculate the status in the reference sheet and then return it. So my initial recommendation would be to create a helper column in your reference sheet and do a COUNTIF on the range of task checkboxes, with the 'if' value being 1. If it equals zero then the status is 'Not Started', if it is greater than zero but less than 5 then it is 'In progress', and if it is 5 then the status is 'Complete'.

    So effectively you have a couple of COUNTIF functions (giving numbers) nested within IF functions (returning statuses).

    You'll then have a much simpler lookup to perform from the other sheet.

  • heyjay
    heyjay ✭✭✭✭✭
    Options

    Please modify as needed:

    =IF(
    SUM(
    IF(INDEX({A}, MATCH(ID@row, {ID}, 0)) = 1, 1, 0), 
    IF(INDEX({B}, MATCH(ID@row, {ID}, 0)) = 1, 1, 0), 
    IF(INDEX({C}, MATCH(ID@row, {ID}, 0)) = 1, 1, 0)) = 3, "Complete", 
    
    IF(
    SUM(
    IF(INDEX({A}, MATCH(ID@row, {ID}, 0)) = 1, 1, 0), 
    IF(INDEX({B}, MATCH(ID@row, {ID}, 0)) = 1, 1, 0), 
    IF(INDEX({C}, MATCH(ID@row, {ID}, 0)) = 1, 1, 0)) = 0, "Not Started", "In Progress"))
    

    {A} ,{B}, {C} = checkbox columns

    {ID} = Reference Column used in Match function.

    3 = Max number of columns with check box.

    ...

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!