Solved: Look-up table: Please help me

[Lester]

Hello:
I wonder if someone out there can help me (I wish the company would send me on a training course, but there you go...). I have a problem and I don't know the neatest way about solving it. Please see the attachment.

Basically, the worksheet called "Charge Rates Look-up" is just that - it's a reference table which defines an individual's charge rate (?/hour) for a given 'Project' and 'Task Code'.
What I need to do is write a macro (if that's the best way to do it) to produce a new column on the 'Bookings' worksheet (in column F) which will be equal to "Time Booked * <Charge Rate retrieved from Charge Rates Look-up worksheet>"

Thanks very much for your help - I really appreciate it.
Hope it's nice and clear to everyone.
Regards
Lester

[Bob Phillips]

In Bookings!F2 use

=E2*SUMPRODUCT(--('Charge Rates Look-up'!$A$1:$A$28=Bookings!A2),--('Charge Rates Look-up'!$B$1:$B$28=Bookings!B2),--('Charge Rates Look-up'!$D$1:$D$28=Bookings!D2),'Charge Rates Look-up'!$E$1:$E$28)

and copy down

[Lester]

In Bookings!F2 use

=E2*SUMPRODUCT(--('Charge Rates Look-up'!$A$1:$A$28=Bookings!A2),--('Charge Rates Look-up'!$B$1:$B$28=Bookings!B2),--('Charge Rates Look-up'!$D$1:$D$28=Bookings!D2),'Charge Rates Look-up'!$E$1:$E$28)

and copy down
Wow! I wish I could understand all that! I can sort of make out how you are referencing each workbook and columns within it.

I'll give it a try...

Thanks for your help.

[Lester]

update:
I think it works fine!
I was just wondering...If another person's details were added to the 'Charge Rates Look-up', would I need to modify the equation/formula above?

[Bob Phillips]

You would only have to make sure that the ranges encompass all of the data.

[Lester]

You would only have to make sure that the ranges encompass all of the data.
Thanks again!
Regards
Lester

[Lester]

Another question, please...for easy of portability, how can I embody the above code in a macro, so that a user can just run the macro to generate the new column data? I tried to create a macro called ProcessLookUp as below. I keep get a syntax error... my thought is that VBA doesn't recognise SUMPRODUCT. Is this the case? Is there a workaround? Please help.
---
Public Sub ProcessLookUp()

With ActiveSheet
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
iStart = 1
.Cells(1, "F").Value = "Labour Charge"
For i = 2 To iLastRow

.Cells(i, "F").Value = .Cells(i, "E").Value*SUMPRODUCT(--('Charge Rates Look-up'!$A$1:$A$28=Sheet6!A2),--('Charge Rates Look-up'!$B$1:$B$28=Sheet6!B2),--('Charge Rates Look-up'!$D$1:$D$28=Sheet6!D2),'Charge Rates Look-up'!$E$1:$E$28)

Next i
End With
End Sub
---

Sorry for the code layout!

[Bob Phillips]

Public Sub ProcessLookUp()
Dim iLastRow As Long

With ActiveSheet
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("F2").Resize(iLastRow - 1).Formula = _
"=E2*SUMPRODUCT(--('Charge Rates Look-up'!$A$1:$A$28=Sheet6!A2)," & _
"--('Charge Rates Look-up'!$B$1:$B$28=Sheet6!B2)," & _
"--('Charge Rates Look-up'!$D$1:$D$28=Sheet6!D2)," & _
"'Charge Rates Look-up'!$E$1:$E$28)"
End With
End Sub

[Lester]

xld...you've done it again. Great stuff. Works a treat!

[Bob Phillips]

And not a loop in sight!

[Lester]

And not a loop in sight!
That really threw me!