Can you post your workbook, as it stands now, with that current functionality?
OK, Here's what I got. We do our timesheets in excel. I made a Job Log spreadsheet that would pull the hours and work description from all our timesheets so I can see a breakdown of each work description. So on my Job Log, you enter the job number and it will show the hours for a set list of work descriptions. Problem is some users use to overwrite their work descriptions. So what I want to do is when the job number is entered and the hours are populated for the set list of work descriptions, I want any non-typical work descriptions to also populate and list the hours.
The work descriptions are in a named range called Work_Desc on a separate worksheet. Attached is a picture of what I have so far, I would like rows inserted between row 26 and 27 with the non-typical work descriptions and hours. At the same time it needs to only list a work description once if it's duplicated.
So if a user had "My Meeting" as a work description, it should insert between Rows 26 & 27 listing the description and total hours for that description. What I currently do now to tally hours for multiple instances of description is the formula
=SUMIFS(Reg_Hours,Job_Num,$C$2,Work_Desc,$B$9)
Is there an easy way with VBA? Or is there a simpler way? One thing I did test was to copy the Work_Desc range from it's sheet and paste at the bottom of this one, then use remove duplicates. I could do that and copy the formula down but I would rather this be automated if possible.
Any feedback appreciated.![]()
Can you post your workbook, as it stands now, with that current functionality?
AttachedAttachment 1180
Add this array formula to B28 and copy down
=IFERROR(INDEX(Data!$E$1:$E$1000,SMALL(IF((Data!$A$1:$A$1000=$C$2)*(NOT(ISNUMBER(MATCH(Data!$E$1:$E$1000,$B$9:$B27,0)))),ROW(Data!$A$1:$A$1000)),ROW(A1))),"")
You SUMIFS formulae can stay.
What is this doing? I copied it and it turns the cell blank. I notice it's referencing E1:E1000 from the Data tab but that necessarily wouldn't be the case b/c I want it to only narrow down descriptions for the specified job number, right? I just want any work description from the Data tab that does not match the preset ones on the Job Log tab to appear with the hours.
Thanks!
Did you array-enter it? It is an array formula.
I thought i entered it right. How do you array-enter it? I highlighted B28:B33 and then entered it. But I'm not familiar with arrary-enter.
Don't select B28:B33, just B28.
Add the formula, then hit Ctrl-Shift-Enter together, not just Enter.
Fill-copy B28 down to B33.
Last edited by Bob Phillips; 2013-03-07 at 04:24 PM.
Mind -> Blownthank you. Now I notice my hours aren't adding up so that's my next venture to look into.
Take a look at the formula in B12 and B25!
I also think B16 should be =SUMIFS(Reg_Hours,Job_Num,$C$2,Work_Desc,$B$16&"*")
Bookmarks