Link sheet to rotating cell in feeder sheet
My team is trying to create a "wrap up" type sheet that pulls data from child sheets that are updated monthly from a form. The data in the child sheets that needs pulled are the most recent submissions from forms associated with the child sheets which means the cell will constantly change. Is there a way to link these without manually updating the link each month?
Answers
-
Hello Lee - Yes We do something very similar with weekly project updates, where we want summary sheets and dashboard widgets to only show the latest. Here's how we do it. Let's say your data looks like this:
You will want to insert a row at the top to retrieve the row that says 9/22 Black Large Cube, (let's say it's row 1) and then link your summary sheet to row 1 only. Add a helper column to identify which row has the most recent form data. The formulas look like this: =LARGE([Form Submission Date]:[Form Submission Date], 1)
The formulas for the 3 Data columns are:
=INDEX([Data 1]:[Data 1], MATCH([Last Update]@row, [Form Submission Date]:[Form Submission Date]))
=INDEX([Data 2]:[Data 2], MATCH([Last Update]@row, [Form Submission Date]:[Form Submission Date]))
=INDEX([Data 3]:[Data 3], MATCH([Last Update]@row, [Form Submission Date]:[Form Submission Date]))
Hope this helps!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!