Here we go with the correct zone now.

Cheers

Rob

Solved

Posted on 2011-10-09

Hi again experts.

This is in relation to my last question but it now has to retrieve the data based on the Employee ID number chosen.

I have attached an example workbook, the times in RED are what I need to retrieve from sheet "FP Reader" based on the Empoyee no. chosen, and then the times put in the aproprate day, IN or OUT times.

Can you please help...I have been trying for ages to make this work.

May Thanks

Rob

Book-DATA-LOOKUP.xlsx

This is in relation to my last question but it now has to retrieve the data based on the Employee ID number chosen.

I have attached an example workbook, the times in RED are what I need to retrieve from sheet "FP Reader" based on the Empoyee no. chosen, and then the times put in the aproprate day, IN or OUT times.

Can you please help...I have been trying for ages to make this work.

May Thanks

Rob

Book-DATA-LOOKUP.xlsx

17 Comments

Here we go with the correct zone now.

Cheers

Rob

=SUMPRODUCT(('FP Reader'!$H$5:$H$21)*('FP Reader'!$B$5:$B$21=C$4)*('

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)*('

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'!$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/YY

IF($B5="OUT",TEXT($A5,"M/D

See attached demo workbook:

Dave

Book-DATA-LOOKUP-r1.xlsx

I tryed changing it but the formula read #N/A

any suggestions??

How about 1000 rows?

See attached.

Dave

Book-DATA-LOOKUP-r1.xlsx

SUMPRODUCT(('FP Reader'!$H$5:$H$1000

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

Rob.

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

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

=IFERROR(IF(INDEX('FP Reader'!$H:$H,MATCH(C$4&"|

See attached.

Book-DATA-LOOKUP-r1.xlsx

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)*('

rather than:

=SUMPRODUCT(('FP Reader'!$H$5:$H$21

Dave

Book-DATA-LOOKUP-r3.xlsx

Let me know if this suits.

Here's the code:

```
Sub updateFormulaToValues()
Dim wkb As Workbook
Dim wks As Worksheet
Dim myCell As Range
Dim lastRow As Long, lastCol As Long
Dim fRange As Range
Dim firstFormula As Range
Dim entirePasteRange As Range
Application.ScreenUpdating = False
Set wkb = ThisWorkbook
Set wks = wkb.Sheets("Sht 1 - Table 1")
Set fRange = wks.Range("B:B").Find(What:="TOTAL", LookIn:=xlFormulas, LookAt:=xlWhole)
If fRange Is Nothing Then
MsgBox "Could not find TOTAL line", vbCritical, "Aborting..."
Exit Sub
End If
lastRow = fRange.Row - 1
fmula = "=SUMPRODUCT(('FP Reader'!$H$5:$H$1000),('FP Reader'!$B$5:$B$1000=A$4)*('FP Reader'!$J$5:$J$1000=$B1)*(TEXT('FP Reader'!$C$5:$C$1000,""M/D/YYYY"")=IF($B1=""OUT"",TEXT($A1,""M/D/YYYY""),TEXT($A2,""M/D/YYYY""))))"
Set firstFormula = wks.Range("A1")
firstFormula.Formula = fmula
lastCol = wks.Cells(4, wks.Columns.Count).End(xlToLeft).Column
For Each myCell In wks.Range("C4", wks.Cells(4, lastCol))
If IsNumeric(myCell.Value) And myCell.Value <> "" Then 'copy formula down
firstFormula.Copy
wks.Range(Cells(5, myCell.Column), Cells(lastRow, myCell.Column)).PasteSpecial xlPasteFormulas
Application.CutCopyMode = False
'now convert to values
wks.Range(Cells(5, myCell.Column), Cells(lastRow, myCell.Column)).Copy
wks.Range(Cells(5, myCell.Column), Cells(lastRow, myCell.Column)).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End If
Next myCell
Range("A1").Clear
Range("A1").Activate
Application.ScreenUpdating = True
End Sub
```

See attached.

Dave

Book-DATA-LOOKUP-r3.xlsm

Title | # Comments | Views | Activity |
---|---|---|---|

excel forecast function | 1 | 28 | |

Excel File Date Format | 6 | 17 | |

Compare values between two columns and copy matching data to new sheet. | 2 | 19 | |

Copy Value of cell in formula | 1 | 25 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**22** Experts available now in Live!