Update all cross references in formulas across row
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!!
Answers

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 readd 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
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.8K Get Help
 65 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!