LOOK UP DATA BASED ON ID NO. CHOSEN (again)

sorry, I deleted the last question as I put the wrong zone in.
Here we go with the correct zone now.

Cheers
Rob

Ok, here's a solution - pasted in cell C5, then copied down, and copied across to columns F, I and L

=SUMPRODUCT(('FP Reader'!$H$5:$H$21)*('FP Reader'!$B$5:$B$21=C$4)*('FP Reader'!$J$5:$J$21=$B5)*(TEXT('FP Reader'!$C$5:$C$21,"M/D/YYYY")=IF($B5="OUT",TEXT($A5,"M/D/YYYY"),TEXT($A6,"M/D/YYYY"))))

One of the side-effects of this formula and your formatting is the result of zero value.  Zero means no match, but formatted as you have it would return 12:00:00 AM. So, we enhance the formula to check for zero value, and we're done!

=IF(SUMPRODUCT(('FP Reader'!$H$5:$H$21)*('FP Reader'!$B$5:$B$21=C$4)*('FP Reader'!$J$5:$J$21=$B5)*(TEXT('FP Reader'!$C$5:$C$21,"M/D/YYYY")=IF($B5="OUT",TEXT($A5,"M/D/YYYY"),TEXT($A6,"M/D/YYYY"))))=0,"",SUMPRODUCT(('FP Reader'!$H$5:$H$21)*('FP Reader'!$B$5:$B$21=C$4)*('FP Reader'!$J$5:$J$21=$B5)*(TEXT('FP Reader'!$C$5:$C$21,"M/D/YYYY")=IF($B5="OUT",TEXT($A5,"M/D/YYYY"),TEXT($A6,"M/D/YYYY")))))

The formula might be cleaned up a bit, if we were working with the same datatype across all the dates, but for "simplicity", I convert all to text e.g., TEXT(A5,"M/D/YYYY") so all are compared on the same basis.

Let's break the basic formula down:

=SUMPRODUCT(('FP Reader'!$H$5:$H$21)*('FP Reader'!$B$5:$B$21=C$4)*('FP Reader'!$J$5:$J$21=$B5)*(TEXT('FP Reader'!$C$5:$C$21,"M/D/YYYY")=IF($B5="OUT",TEXT($A5,"M/D/YYYY"),TEXT($A6,"M/D/YYYY"))))

('FP Reader'!$H$5:$H$21) - is the time that we want as the result
('FP Reader'!$B$5:$B$21=C$4) - checks the employee ID number
('FP Reader'!$J$5:$J$21=$B5) - checks for match on IN or OUT
TEXT('FP Reader'!$C$5:$C$21,"M/D/YYYY")  - sets up the date check, and
IF($B5="OUT",TEXT($A5,"M/D/YYYY"),TEXT($A6,"M/D/YYYY")) - compares that date to the correct row - the one having "OUT" in it, as it holds the date in column A

See attached demo workbook:

Dave


Book-DATA-LOOKUP-r1.xlsx

Thanks,  only thing is I need to allow for alot more potinual data. eg.I need to look at all of column H from H5 downwards.
I tryed changing it but the formula read #N/A

any suggestions??

I just had to coerce a null value (due to your if statement on FP Reader, re: time clocked in/out) to a zero in the null scenario.

How about 1000 rows?

See attached.

Dave
Book-DATA-LOOKUP-r1.xlsx

Sorry formula puts #VALUE! if it looks at rows with no data

Would you like to look at what I posted?

Dave

Just to be clear, I corrected the issue you teed up with #VALUE.  I concatenated the H5:H1000 parameter with 0, thus:

SUMPRODUCT(('FP Reader'!$H$5:$H$1000&0) which eliminates the error in the SUMPRODUCT when you introduce the NULL value in the FP Reader sheet.

As a result, your formulas should now be clean, and they are set for 1000 rows.  Feel free to increase the number of rows, as needed.

Please advise if you have any additional issues.

See attached,

Dave
Book-DATA-LOOKUP-r2.xlsx

Thanks for your help...your formula worked, but is to slow. When i import the data into 'FP Reader' the formulas are tryng to run and slows the import and sorting of inital data. Is there a way of runing this with code, say on a button or something, or do you have any other suggestions?

Rob.

Not for points....

Rather than repeat the formula to eliminate 12:00:00 AM one option is to custom format result cell, i.e.

hh:mm:ss AM/PM;;

note semi-colons

regards, barry

It looks like you're using 2007 or 2010, so you can use tables, which makes the formulas a bit easier to follow. So I put your timesheet data list into a 'table' so that the ranges can be referenced with names and not ranges. This can be a big help if you just want to add data to that table. but it also makes the formulas easy to understand, so that you can change them once you see how things work

Next on your calendar page where you want the data to sum:
 the lines that say the day of the week *next to 'IN' were hardcoded as text value days. But if you change those cells to the date you want to lookup (the date listed in cell below it)' then you can use that 'day name' cell as your match value. Then you don't need to make your formula any longer than necessary. you only need to change the cell format to DDDD to show the name of the day.

next when trying to match the punch in/out times to the day that's being reported, all you need to do here is match the integer value of someone's punch in time to the report date that you are looking up.
for example the excel true number value of 8/31/11 5:10am 40786.22. and the date you want to lookup might be 8/31/11 (excel value 40786). So the int function is probably quickest way to get to the day information in a date/time value.

another format trick... if there are no matches the time will sum to zero, which with time formatting looks like 12:00. but cell formatting lets you specify how you want positive, negative and zero values to look. so I changed the cell format to show a time format for positive numbers and a null value for zero values. (right click and select cell formatting to see how that works) No formula necessary to clean up the non-matches (zeros)

Finally sumproducts are used in the formulas because they let you sum with multiple criteria and let you manipulate the data you are looking up (like the int function part) but this means that each formula is a mini array. Not sure how big your data is.. but it could get slow if it gets huge.
And this also means that if you need to change these formulas, instead of typing enter after making changes, you need to hit control+shift+enter. you will know you did it right if brackets appear around the formula in the formula bar.
 Book-DATA-LOOKUP-with-sumprods.xlsx

@J79123,
Your SUMPRODUCTS do not need array-entering, BTW.

Regards,
Rory

@dlmille,
FYI, in this instance rather than using &0 in your SP formula, you can just replace the * with a comma and text and empty cells will be ignored:

=SUMPRODUCT('FP Reader'!$H$5:$H$21,('FP Reader'!$B$5:$B$21=C$4)*('FP Reader'!$J$5:$J$21=$B5)*(TEXT('FP Reader'!$C$5:$C$21,"M/D/YYYY")=IF($B5="OUT",TEXT($A5,"M/D/YYYY"),TEXT($A6,"M/D/YYYY"))))

rather than:

=SUMPRODUCT(('FP Reader'!$H$5:$H$21&0)*('FP Reader'!$B$5:$B$21=C$4)*('FP Reader'!$J$5:$J$21=$B5)*(TEXT('FP Reader'!$C$5:$C$21,"M/D/YYYY")=IF($B5="OUT",TEXT($A5,"M/D/YYYY"),TEXT($A6,"M/D/YYYY"))))

Thanks, rory, that's a good tip.

Dave

@RobJanine - Please try this solution, incorporating input from barryhoudini and rorya.  Let's see if this is fast enough.  Otherwise, I can look into a macro to update the data.

Dave
Book-DATA-LOOKUP-r3.xlsx

Thankyou all so much for all your work.

rob.