Database Functions

  • Thread starter Thread starter Polly
  • Start date Start date
P

Polly

Hello all, I'm hoping someone can help me figure this out. I have
tried vlookup, lookup, if statements and DGet - with no luck. What I
have:

Workbook with 3 spreadsheets.

Sheet 1 contains entries of hours worked with the following relevant
fields:
Member ID, Week Ending, Hours

Sheet 2 - Detailed info on members

Sheet 3 is a monthly report that I need to hand out detailing the
hours entered per month:

A1 - Member ID

B1 - Weekending

B2 - Hours worked

So, I need to search sheet 1 where the Member ID (A1) and Week Ending
(B1) match and then return into sheet 3, the hours worked (B2)

Sheet one will eventually contain 52 weeks for each of our 30 members.
But there will only be one weekending for each member, ie - Member one
will have only one entry for weekending 10/7/2007, etc... so both
criteria must match.

Thank you.

Polly
 
Polly said:
Hello all, I'm hoping someone can help me figure this out. I have
tried vlookup, lookup, if statements and DGet - with no luck. What I
have:

Workbook with 3 spreadsheets.

Sheet 1 contains entries of hours worked with the following relevant
fields:
Member ID, Week Ending, Hours

Sheet 2 - Detailed info on members

Sheet 3 is a monthly report that I need to hand out detailing the
hours entered per month:

A1 - Member ID

B1 - Weekending

B2 - Hours worked

So, I need to search sheet 1 where the Member ID (A1) and Week Ending
(B1) match and then return into sheet 3, the hours worked (B2)

Sheet one will eventually contain 52 weeks for each of our 30 members.
But there will only be one weekending for each member, ie - Member one
will have only one entry for weekending 10/7/2007, etc... so both
criteria must match.

Thank you.

Polly

Hi, Polly
Click to expand...

Have you tried doing a search of the various rows using "SUMIF"?

For instance, if the Members IDs are in column A, Week Ends in Column B, and
hours worked in column C, then, assuming each column contains (say) 20 names,

Let the code in cell A21(say) be :

=if(sumif(A1:A20,"member ID 1",if(Sumif(B1:B20,"Weekend in
question",C1:C20),"","")

The brackets & etc. may want sorting, as it is a bit off the cuff, but the
basic structure is sound - I've used this type many times.

Basically, the first expression scans the names in A1 to A20, and, where
there is a match, scans the weekend in those rows. If the weekend also
agrees, then it puts the hours into the "sum". When it completes the scan,
the total of hours worked for that individual for that week end will be given.

For cell A22, the parameters could be altered for a different mamber ID, or
a different week end. It's a bit long-winded, but look on the bright side -
you only have to do it once!

Good luck
 
It might be worth to look into using SQL. It may need a bit of learning
but it will make these queries a lot easier. Excel could still be the
database or you could move to a real database.

RBS
 
It might be worth to look into using SQL. It may need a bit of learning
but it will make these queries a lot easier. Excel could still be the
database or you could move to a real database.

RBS

















- Show quoted text -
Click to expand...

O would love to use SQL and put it into a real database - but my
program director refuses to discuss the idea. He doesn't know enough
about them...but then again, he can't ever understand the excel
formulas I use either!! - Pete, I will try the sumif - seems like it
would work. I'll let you know!

Thanks

Polly
 
O would love to use SQL and put it into a real database - but my
program director refuses to discuss the idea. He doesn't know enough
about them...but then again, he can't ever understand the excel
formulas I use either!! - Pete, I will try the sumif - seems like it
would work. I'll let you know!

Thanks

Polly- Hide quoted text -

- Show quoted text -
Click to expand...

Ok - I used this:

=SUMIFS('Hours Worked'!$G:$G,'Hours Worked'!$A:$A,'Member Report'!$B
$1,'Hours Worked'!$E:$E,'Member Report'!$A7)

new function provided with Excel 2007 - sumifs(sum_range,
criteria1_range, criteria1, criteria2_range, criteria2...etc)

which worked perfect! - Thanks for the help :)

Polly
 
You don't have to put it in a database to use SQL.
Your program director may find SQL easier than
formula's.

RBS
 
Back
Top