Link sheet to rotating cell in feeder sheet

Lee Dunham

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?

Scott Peters

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!