Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database Functions
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database Functions
"Polly" wrote: 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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database Functions
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 "Polly" wrote in message ps.com... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database Functions
On Nov 1, 6:42 pm, "RB Smissaert"
wrote: 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 "Polly" wrote in message ps.com... 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- Hide quoted text - - Show quoted text - 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database Functions
On Nov 1, 6:48 pm, Polly wrote:
On Nov 1, 6:42 pm, "RB Smissaert" wrote: 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 "Polly" wrote in message ups.com... 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- Hide quoted text - - Show quoted text - 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 - 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database Functions
You don't have to put it in a database to use SQL.
Your program director may find SQL easier than formula's. RBS "Polly" wrote in message ps.com... On Nov 1, 6:42 pm, "RB Smissaert" wrote: 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 "Polly" wrote in message ps.com... 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- Hide quoted text - - Show quoted text - 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Database Functions | Excel Worksheet Functions | |||
Database Functions | Excel Discussion (Misc queries) | |||
efficiency: database functions vs. math functions vs. array formula | Excel Discussion (Misc queries) | |||
Database Functions | Excel Discussion (Misc queries) | |||
Database functions | Excel Worksheet Functions |