• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

vlookup help, please. See attach file

B

BigD

Member
I will do my best to clearly explain what I need:

"Dashboard" tab needs to calculate the hours for each employee based upon the time sheet hours on tab "Time Sheet_2014-5-14" and calculate the employees who only charged time to codes (work oders) found on "WOs" tab.

Thank you for viewing my thread.
 

Attachments

Hi BigD..

Is this your real name.. or type of data you use daily. :)

In C5.. use CSE Formula as..

=SUM(SUMIF('Time Sheet_2014-5-14'!C2:MN2,TRANSPOSE(WOs!$E$6:$E$3390),OFFSET('Time Sheet_2014-5-14'!$A$1,MATCH(Dashboard!$B5,'Time Sheet_2014-5-14'!B:B,0)-1,2,1,350)))

Confirm the formula by pressing Ctrl + Shift + Enter, not just Enter..

Set calculation mode to Manual recommending.. due to heavy size and volatile function.. :(
 

Attachments

wow, massive file. Thank you for helping.

This seems to be working other than the major lag. This is not possible to calculate with vlookup?
 
Hi ,

The list of names on the Dashboard includes all the names on the Time Sheet tab , with the exception of the first name : 1439 - Chapman, Joseph E

The names are also in the same order in both tabs.

Are these two facts coincidence , or will this be the case always ? If so , the formula can be simplified.

Narayan
 
ack, thats a human error. The list on the timesheet should be the exact same list on the dashboard. I just missed the top name when I copied and paste. Thank you for looking into this as always.
 
Then in this case..

Formula can be simplified like..

Again, confirm the formula by ressing Ctrl + Shift + Enter, not just Enter..
=SUM(SUMIF('Time Sheet_2014-5-14'!C$2:MN$2,TRANSPOSE(WOs!$E$6:$E$3390),'Time Sheet_2014-5-14'!$C6:$MN6))
 
One issue today. I pasted a new timesheet on the timesheet tab, click on "Dashboard" tab, hit ctrl+shift+enter and all I got were 0's for all the times. Any idea what I may be doing wrong?

See attached file.
 

Attachments

Last edited:
I have tinkered with this over the weekend without any success. Surely it has to be something simple that I am doing wrong?
 
I also just recently ran across another issue. It is calculating hours for employees who do not have time enter on the time sheet (e.g. Colchado, Jose). I am deeply apologzie for multiple post, but I am in desperate need to get this working. It saves ~6 hours of work if I can get this file to work correectly. Thank you so much for your time.
 

Attachments

Hi BigD!

Sorry from my side too.. due to late response.. :(

Please check the attached file..
actual Formula provided there..

and for later use..

=IFERROR(SUM(SUMIF(TopCellContainToBeCheckValues,TRANSPOSE(NeedToCheckValues),OFFSET(StartingPositionofFoundEmployeeName,MATCH("*"&EmployeesPartialName,EmployeesFullName,0)-1,1,1,NumberOfCellinTopCell))),0)

I think, now you can adapt it in any circumstances..:)

PS: Please check Calculation Option.. change to AUTOMATIC from ManualCalculation Mode..
 
It seems to be working flawlessly, Debraj. This file has been very helpful in speading up a process for me. Thank you again for your time and dedication to the forums. I wish I was half as savvy as you ninjas are.
 
You must log in or register to reply here.
Back
Top