Hi Will
Welcome to Excel Guru forums
It be easier to advise you if you attach a small sample. It might be worth using a macro here to consolidate the data instead.
From what I can see however you might want to avoid direct references in favour of other methods. For instance you can return a list of dates, ordered ascending, using the SMALL worksheet function. So if your dates are in column M of the Sarah worksheet then:
=SMALL(Sarah!M:M,1)
The above will yield the smallest date from column M.
=SMALL(Sarah!M:M,2)
The above will yield the 2nd smallest date from column M.
Conversely you can use the LARGE worksheet function to return the dates ordered descending.
So for a dynamic list of dates you can exploit the ROW function. The following example entered in A2 of your consolidation sheet and copied down:
=SMALL(Sarah!A:A,ROW($2:2))
The next step would be to use VLOOKUP's for instance to return the values for the remaining fields. It's hard to suggest how exactly which is why I suggest you provide a sample.
Hope this helps.
Bookmarks