What formula to use to link status changes in two different sheets

sleepysapling

Hello,

I want to link 2 cells in different sheets so that when I change the Status of one particular row to "Complete", it will automatically change the Status in another sheet to "Approved". I have been trying to use the INDEX(MATCH function but have not been successful yet. I'm hoping someone can give me some help as to how to make this actually work. Thank you.

Georgie

i @sleepysapling,

You can achieve this by combining the IF function with an INDEX(MATCH) formula. 

For INDEX(MATCH) formulas to work, you need to have a matching value that ​​must be unique across both sheets and stored in the same column type. That is, the value will be unique to one row in each sheet but match exactly between the two sheets, so that the MATCH function can locate the relevant row. 

It’s not clear from your screenshots if you already have this in your sheets, so if you don’t, you could use either an auto-number column or a column where you combine values from other columns in your sheet, eg a Row ID + Task Name (your formula for that column would be something like =[Row ID]@row + “-” + [Task Name]@row, but using the relevant column names from your sheet, and you can add more columns into the formula if desired). 

In my test sheets, I’ve used an auto-number column to create my matching values, and I’ve called this “Task ID” in the source sheet and “Task number” in the destination sheet.

I set my source sheet up with similar values to yours, as seen below:

Then, in the Project Sponsor Approval column in my destination sheet, I used the following formula:

• =IF(INDEX({Status}, MATCH([Task number]@row, {Task ID}, 0)) = "Complete", "Approved")

The elements in curly brackets {} are cross-sheet references. You can name these as desired, but to clarify, the references in the above formula are as follows:

• {Status} is the entire Status column in the source sheet 
• {Task ID} is the entire Task ID column in the source sheet

You can see the formula in the destination sheet here, showing that it’s also referencing the Task number cell in the same row as the value it needs to find a match for in the source sheet:

I then converted this to a column formula and confirmed that the approval column updates as desired - displaying “Approved” if the matching Task ID in the source sheet is set to “Complete”:

You can check out the following resources for more information on the functions within the formula and how to use different functions together, such as INDEX and MATCH:

• IF Function
• INDEX Function
• MATCH Function
• Formula combinations for cross sheet references

Hope that helps!

Georgie

kowal

hi @sleepysapling,

I would use a helping column in the destination sheet that is directly celllinked with source values.

So you have extra column destination Sheet that will change to Complete (whenever source data is complet) and in the Project Sponsor Approval I would use =if function so if the data in Helping column is complete change the Project Sponser to Approved etc.

Georgie

i @sleepysapling,

You can achieve this by combining the IF function with an INDEX(MATCH) formula. 

For INDEX(MATCH) formulas to work, you need to have a matching value that ​​must be unique across both sheets and stored in the same column type. That is, the value will be unique to one row in each sheet but match exactly between the two sheets, so that the MATCH function can locate the relevant row. 

It’s not clear from your screenshots if you already have this in your sheets, so if you don’t, you could use either an auto-number column or a column where you combine values from other columns in your sheet, eg a Row ID + Task Name (your formula for that column would be something like =[Row ID]@row + “-” + [Task Name]@row, but using the relevant column names from your sheet, and you can add more columns into the formula if desired). 

In my test sheets, I’ve used an auto-number column to create my matching values, and I’ve called this “Task ID” in the source sheet and “Task number” in the destination sheet.

I set my source sheet up with similar values to yours, as seen below:

Then, in the Project Sponsor Approval column in my destination sheet, I used the following formula:

• =IF(INDEX({Status}, MATCH([Task number]@row, {Task ID}, 0)) = "Complete", "Approved")

The elements in curly brackets {} are cross-sheet references. You can name these as desired, but to clarify, the references in the above formula are as follows:

• {Status} is the entire Status column in the source sheet 
• {Task ID} is the entire Task ID column in the source sheet

You can see the formula in the destination sheet here, showing that it’s also referencing the Task number cell in the same row as the value it needs to find a match for in the source sheet:

I then converted this to a column formula and confirmed that the approval column updates as desired - displaying “Approved” if the matching Task ID in the source sheet is set to “Complete”:

You can check out the following resources for more information on the functions within the formula and how to use different functions together, such as INDEX and MATCH:

• IF Function
• INDEX Function
• MATCH Function
• Formula combinations for cross sheet references

Hope that helps!

Georgie

sleepysapling

Thank you @Georgie ! Very helpful and detailed information I appreciate it! I will test some of this out and see if it works for our purposes. Thanks!