Update all cross references in formulas across row

evp

I'm attempting to build a summary sheet that pulls the same milestone names status (column 9 in reference sheet) (maybe on different rows per sheet hence the vlookup) from different cross referenced sheets per row to display on a dashboard. Right now my vlookup works but there are 20 milestones per row that need to be updated with the correct rows reference sheet and correct columns milestone. What is the best way to accomplish this?

example:

Thanks!!

Genevieve P.

Hi @evp

One way to make this a bit easier would be to add the Milestone name that you have "in quotes" in your formula to a top row in your sheet. That way you can reference the cell at the top of this column instead of manually typing the word into the formula:

[Milestone 1]$1

This has an absolute reference ($ sign) in front of the row number so that as you update your formulas it stays looking at that top row.

Then if you drag that formula over to the right into Milestone 2's column, the cell reference will update:

This means you'll only need to build the formula once per referenced sheet, then you can drag it across all 9 columns and it will automatically update.

I will note that since you're using a VLOOKUP, it's referencing 9 columns of data per sheet. If you have a lot of sheets / rows, this could start to slow down your sheet as it processes this data. You may want to look into using an INDEX(MATCH formula instead.

INDEX(MATCH uses two separate column references and doesn't care about any of the columns in the range between those two. This allows for faster processing times and for you to rearrange columns in your source sheets without it breaking your metrics.

However, you would need to delete and re-add 2 new references per sheet for each of your Sheet rows:

=INDEX({Sheet A Value to Return}, MATCH([Milestone 1]$1, {Sheet A Column 9}, 0))

See: Formula combinations for cross sheet references

Cheers,

Genevieve