Automatically Referencing Data in Different Tabs

[jsboss]

All,

I'm designing a sheet that has to reference some specific information from one tab and display it on another tab, and I can't figure out the specifics. I've attached an example that shows what I need...

This sheet tracks different units, each with a specific reference #, that each have random numbers of tracks (that is, some units have three tracks, some have two, some have one in no specific pattern or order).

TAB1 records overall statistics for different units (each with a specific reference #)

TAB2 records statistics for individual tracks within units (each tied to a specific parent reference #).

For user purposes, I need some specific data from TAB2 to appear in TAB1 automatically. The attached EXAMPLE shows what I need, and has sample data for demonstration purposes.

Here’s what I need in the cells that are currently blank in TAB1:

CELL D2: needs to return the First Track start time from TAB2 that matches reference # A3000X234. In this case I need D2 to return “13-Aug-14 21:55”, which is the value from TAB2 CELL C2.

CELL E2: needs to return the Last Track end time from TAB2 that matches reference # A3000X234. In this case I need E2 to return “14-Aug-14 17:34”, which is the value from TAB2 CELL D4.

CELL F2: needs to return the sum of the track times from TAB2 that matches reference # A3000X234. In this case I need F2 to return “18:22”, or the sum of TAB2 E2:E4.

The blank cells in ROW 3 and 4 have to do the same thing for their individual reference #s. The really tricky part is that the number of tracks per unit is not consistent: in this example reference # A3000X235 has two tracks and reference # A3000X236 only has one (A3000X234 had three). Each unit will have a random and unpredictable number of tracks as time goes on.

Is that possible? Any thoughts or suggestions? Please let me know if you need any clarification or have any follow up questions. Thanks in advance!

jsboss

[sktneer]

Try this......

In D2

Please Login or Register  to view this content.

and copy down.

In E2

Please Login or Register  to view this content.

and copy down.

In F2

Please Login or Register  to view this content.

and copy down.

Is this what you are trying to achieve?

[jsboss]

Thanks for the quick reply! I think this will work, but let me get it into the actual sheets. I'll post again if I have problems. Thanks again this is a big help!