Is there a way to update referenced sheets using vlookup and/or index and match?

summetg

Hi there,

We have an issue where we provision a project from our intake sheet, but after it hits the project roll up sheet, we can't update the status. Here's a summary of how our workflow:

1. Intake listing sheet - has our projects intake, which eventually end up as an "approved project". Each initiative has a unique ID which is carried through the lifecycle
2. Assessment roll-up sheet - summarizes our initiative assessments
3. Project portfolio roll-up sheet - where our projects get listed after its created via control center. We also have some project objects that hold a summary of the project and is adjusted by the team. One item of interest is a project charter object.

What we are looking to do is update the current status on sheet 1, and it subsequently updates sheet 2 and 3. The issue is that when the project is created, sheet 2 changes the status to "Confirmed", breaks any linkages to 1 or 3. Is there a way we can do something like a look up of selected number of sheets with the initiative ID and ensure it all matches back to the current status in sheet 1?

I can provide more details if required.

Heath Hilton

summetg,

This sounds similar to some trouble that I have had recently. I was trying to reference cell data that could be on either one of two sheets and I needed a formula that could handle trying (and failing) to find anything on the first sheet and then move onto the second sheet if needed.

Does that sound like it could help? I will share it just in case it may help you or others.

=IFERROR(VLOOKUP([Job Number]@row, {Sheet 1}, 2, false), VLOOKUP([Job Number]@row, {Sheet 2}, 2, false))

Basically, it goes out and checks to see if the cell being looked for (Job Number in this case), is on Sheet 1. If there is an 'error', like when the cell wasn't there, it will jump to the second VLOOKUP and try it on that sheet. I also notice that it works better if it is a "column formula".

Let me know if this helps.

Heath Hilton