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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 348 Global Discussions
 199 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 455 Show & Tell
 28 Member Spotlight
 1 SmartStories
 282 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!