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

emoser1978

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!

Philip Robbins

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

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.