How to lookup rows and sun totals?

[Steven]

i would like to lookup rows of data from a sheet and display the totals to
another sheet of matching data.

the data would look something like this:

code name ref
H:M
A Fred 0001 10.00 11.40 01:40
B Fred 0001 11.40 12.10 00:30
A Joe 0002 10.00 12.30 02:30
B Joe 0002 15.45 16.15 00:30
G Tom 0003 10.25 12.25 02:00
G James 004 10.15 12.15 02:00
B James 004 12.15 12.45 00:30
A Stephen 123 09.40 10.55 01:15
A Sam 5544 10.10 11.00 00:50
B Sam 5544 09.40 10.10 00:30
D Mel 001 12.45 14.45 02:00


could someone please tell me how i could lookup the `code` and get the
totals for all the matching rows with the same `code` for `H:M`

Many thanks,

Steve

 

[Steven]

`Code` should be over A,B and `H:M` should be over far right value

 

[Anders S]

Hi Steven,

Have you tried a Pivot Table? I think it will do what you want (and lots more).

You will find some instructions at www.contextures.com.

HTH
Anders Silven

 

[Steven]

i will have 16 sheets in total to get totals from, can you use pivot table
for that?

Many thanks,
Steve

Hi Steven,

Have you tried a Pivot Table? I think it will do what you want (and lots
more).

You will find some instructions at www.contextures.com.

HTH
Anders Silven

 

[Steven]

Pivot will not work, says theres no field names

Hi Steven,

Have you tried a Pivot Table? I think it will do what you want (and lots
more).

You will find some instructions at www.contextures.com.

HTH
Anders Silven

 

[Anders S]

"theres no field names" is probably because some headers are missing. The header row must be something like

code name ref time1 time2 H:M

or the Pivot Table will protest.

Regards
Anders Silven

 

[Alan Beban]

What's being totaled? E.g., what's the total for code D?

Alan Beban

 

[Steven]

02:00

What's being totaled? E.g., what's the total for code D?

Alan Beban

 

[Steven]

ok i have managed to work out the formula, but now need to hide rows that do
not have a value in the total cell, any way to do this ?

Many thanks,

Steve

 

[Anders S]

16 sheets... hmmm, you didn't say that.

I don't thing you can have a Pivot Table pull data from more than one sheet, at least not easily.

Consolidating data from 16 sheets is a separate issue that requires more info to answer. Please post back with more details about your setup and what you want to to. And why you have 16 sheets.

Regards
Anders Silven