Hello All, I'm a newbie to the forum, thanks for checking out my thread.
Here is my issue. I have a workbook, the first worksheet is a grid with employee names down the left hand column, calender dates across the top. My second worksheet is used as a data sheet, I have written a query that goes to a database and returns records that provide Employee Name, Date, and hours worked.
I get 1 record per day per employee making a big list, ie:
Bill Smith 08/17/08 8.5
Bill Smith 08/18/08 8.5
Bill Smith 08/19/08 8.5
How can I create a formula on the first page that will reference the name and date and look to the second sheet for the hours worked. I've been looking on-line and it looks like I need a Vlookup with a Match all in the same formula, but I'm not sure what should reference what?? Any Ideas?
Try something like:
=Index(Sheet2!$C$1:$C$100,Match(1,(Sheet2!$A$1:$A$100=A1)*(Sheet2!$B$1:$B$100=B1),0))
Where Sheet2!A1:C100 contain your query extract and you are matching Name in A1 to Column A of the query, B1 to Column B of the query.. and getting amount from column C.
NOTE: Adjust ranges to suit and then you must confirm the formula with CTRL+SHIFT+ENTER not just ENTER you will see { } brackets appear.. Then copy down.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Thanks for such a quick reply, I will try out your suggestion right now, be back soon
The formula works great, Thanks a bunch. I will have to look up info on "index", I've never used that before.
Do you think that I could used named ranges for the ranges on sheet 2? That would help because the query brings back different number of records for each week, depending on how is working.
Sure you can....
Here are some instructions on how to create dynamic defined ranges, in case you are unsure...
http://support.microsoft.com/kb/830287
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
What about if I run into a missing record on the query side? IE Bill doesn't work on Wednesday, so that record is missing, the formula results are: = #n/A
To avoid the #N/A error, you can use an error trap that returns what you want, e.g. a null....
=If(Isnumber(Match(1,(Sheet2!$A$1:$A$100=A1)*(Sheet2!$B$1:$B$100=B1),0)),"",Index(Sheet2!$C$1:$C$100,Match(1,(Sheet2!$A$1:$A$100=A1)*(Sheet2!$B$1:$B$100=B1),0))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
This is what I ended up with:
=IF(ISERROR(INDEX(QueryHours,MATCH(1,(QueryName=Payroll!A5)*(QueryDate=Sat),0))),0,(INDEX(QueryHours ,MATCH(1,(QueryName=Payroll!A5)*(QueryDate=Sat),0))))
Seems to be working great, thanks for all your help
That's fine too... but it is not necessary to repeat the INDEX() part as you are only looking to find a Match()...
And many Excel experts agree that it is better to look for the positive (ie. Isnumber(Match()) rather than the negative (i.e. IsErr(Match())... reason: efficiency
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
so I should take out the first Index, and change the iserror to an is number? That would be good if I could speed up the process, we have about 60 employees times 7 days in a week, that formula chugs for a little bit.
If you do that, you also have to switch the result if TRUE/result if FALSE arguments to the IF() function...
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Gotcha, Thanks a bunch
you can also try this one.
CTRL+SHIFT+ENTER=SUMPRODUCT((sheet2!A2:A11 = A1)*(sheet2!B2:B11 = B1),sheet2!C2:C11)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks