Hey,
I am using excel for entering daily earnings. In that we give OT for the employees according to their earnings. In one sheet i enter all data and in another sheet i need the the value of employee Over Time on particular date for particular employee name.
I have attached the file please help me. I don't think for VB code.
Regards
Charles
Last edited by ebin charles; 05-31-2010 at 07:01 AM.
Is it the total overtime for each person you are trying to establish?
or
Do you need to know the overtime hours for each person every day?
Yes
I am looking for the value on the particular date for the particular person.
i need the total overtime for each person for everyday seperately.
Regards
Charles
Hi Charles
I'm not to good with formulae, but here is a possible solution using a little VBa.
Please give it a try.
Click on the name you want in the lower table to see the result.
Repeat this as often as you need.
Click on A2 (Toggle Filter) to reset.
If this is any use to you let me know and I will make all the lists dynamic.
Hope this helps.
If you need any further information, please feel free to ask further
However
If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.
Based on your sample file and using formulae.
If we assume you need to permit backwards compatibility (ie no SUMIFS) then I would suggest you use concatenation to avoid need for SUMPRODUCT, eg:
J3: =$B3&"@"&$C3
copied down
Your summary formula is thus a basic SUMIF
K22: =SUMIF($J$3:$J$18,K$21&"@"&$K$20,$I$3:$I$18)
copied across
(you might also want to consider using a Pivot Table....)
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Thank you sir.
But i need separately in date wise as I attached.
For example if the person name is 719 Ramamoorthty and if worked on
04.04.2010, his OT Value on the particular date should show in the cell.
For this if there is no formula, then vb code is ok.
Please help me
Regards
Charles
Dons' fomula method does exactly that.
I have added it to the workbook attached to this post.
I am still a bit lost as to why you would want to do this, when you can use Autofilter, it seems like double work to me.
Cheers
Thank you so much this what i need.
I have more than 300 employees. Now i need to modify this for all personnel.
There after if I get any doubt on this regard, I contact u.
Thank you once again.
charles
Sir,
If There is double date. example employee worked 04.04.10 in two sites
04.04.10 719 Ramamoorthy
04.04.10 719 Ramamoorthy
how can I show this. Any Idea to add these dates OT value in one cell and display?.
Regards
Charles
The question isn't very clear - the pre-existing solution already caters for duplicate records given SUMIF aggregates.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks