Excel 2007 Question

cbumatay
cbumatay used Ask the Experts™
on
Hello,
I'm looking for a resolution to a problem I'm having in creating an Excel 2007 timesheet.  Here's what i want to do.
There is a large list of codes one can select to charge labor too.  I created a drop list with the selected codes to choose from, which is about 20-30 codes in the list.  Also, hours are entered manually for a specific code.  The remaining hours not allocated manually are distributed to a number of codes automatically and calculated against specific codes with percentages.

What I'd like to do is whenever I manually select a specific code to assign hours manually that the selection triggers and changes the percentage value to 0 (zero), so that hours are not allocated twice for the specific code.  Another way alos to attack this is a simple check box to turn the percentage to 0 for the specific code.

If you ned more info let me know.  Look forward and appreciate your help on this matter.

Thanks,
Chris
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
cbumatay,

It would be useful to see some sample data and/or a sample file.  EE now allows you to directly upload files to your question.

The sample file should clearly illustrate both the input and the expected output given that input.  The sample file need not be very large, but it should have enough examples to cover the expected range of values/scenarios.

Please be advised that once you upload a file, it can be publicly accessed, and that it may not be possible to fully and permanently delete it.  The file may also be indexed by the major search engines.

Therefore, be very careful about posting proprietary, confidential, or other sensitive information.  If necessary, use "fake" and/or obfuscated data in your sample.

Please note that at present EE restricts uploads to certain file types.  If your file type does not match those in the list, you can use http://www.ee-stuff.com instead, which is not officially an EE site, but is run by people connected to EE.

Patrick
cbumatayController

Author

Commented:
Here is the file.  Thanks.
Timesheet.xlsx
cbumatayController

Author

Commented:
Really need all of your help on this one.  If I can get this to work then I'm done and I can submit it.

Thanks ahead of time.

Chris
Hello cbumatay
I downloaded your file and seems you have some formulas errors for 2 reasons
1) you have a circular refrence error (this we can tackle later)
2) the value of cell C3 shows REF resulting in lots of errors.

So my question to you what is the value of C3 and then we can move on
gowflow
After looking at the sheet again have following comments:
1) Your circular refrence errors points to Cell AG23 where in Col AG from row 7 to row 56 you have it pointing to $AG$23 looking at the header title it says Pay Period Hours so I presume it should be refering to Cell W23 and not AG23 changing all those rows eliminated the error. Pls confirm that indeed it should refer to cell W23 and not AG23.

2) in some lines in your time sheet you have  missing formulas I filled them from the previous,line beeing in the same logic

3) the 2 dates that are missing are cell C3 as indicated earlier and Cell C37 which I presume are starting dates I put in C3 1/1/2011 and it filled the table correctly then I filled C37 with 1/15/2011 but in table OVERFLOW TIME RECORD starting row 63 wich is supposed to be similar to the previous table OVERFLOW TIME RECORD strating at row 33 the Cell C67 refer to formulas U25 which is Total regular hours I think you have a problem here !!!

waiting for your comments.
gowflow
cbumatayController

Author

Commented:
Here is the entire worksheet.  I didn't include some of the sheets.
Sorry, I left sheets out.

Chris
cbumatayController

Author

Commented:
you attached 2 which one is it ?
gowflow
cbumatayController

Author

Commented:
Doubled by accident.  Both the same.
ok checked the file your worksheet sheet is +/- the file you first posted and my comments still apply. I cannot answer you on something you need to do I the file you post has errors we need to fix them first then me understand how the whole thing works then I will be better able to assit u
Awaiting for you to answer point 1 point 2 still valid also and point 3 is ok as yourefer to the date you have in U11

gowflow
Any news on Point 1 and 2 of my comment ID: 36334517
gowflow
cbumatayController

Author

Commented:
Ok, How does the workbook look now.  What you were seeing earlier were old checks and balances on number 1.  The workbook should be ok now.
Timesheet--2011-Revised.xls
ok fine can you pls describe clearly what you need to acheive now that I have the workbook with an clear example with row numbers ?
gowflow
See if this is what you want:

Right-click on the sheet name tab
select view code
paste the given code at the end of the code pane
close the VBA window.

Now change the key code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 25 And Target.Count = 1 Then
    If Target.Row > 10 And Target.Row < 29 Then
        Target.Offset(0, -22).Resize(1, 7).ClearContents
        Target.Offset(0, -13).Resize(1, 7).ClearContents
    End If
End If
End Sub

Open in new window

Hello  cbumatay,
I have checked your file and made lots of formulas corrections for missing formulas and also for wrong formulas pointing in table 2 to table4

I have introducted a commandbutton on top under col AC in row 2-3 called Clear Worksheet Data and as its name indicate it clears all the input areas in wour worksheet to prepare for new input. I does not clear the formulas though.

Reading your question several times I concluded the following:
You need to input manually time in the first table from row 11 to 28 and the time you want to input is associated to codes that you choose from in col Y from row 11 to 23 and you want when you choose a code for that code to be used all over the worksheet ONCE !!! I noticed that from table 2 onward you have all the codes that are affected to time in prorata.

So if my understanding is correct to acheive what you want I introduced a new column called Occurence AF to which the initial value is 1 and I changed in table 2 to 4 all the formulas for coil V to take this occurence in concideration. by default the value being 1 all the codes are affected as you have originally set them up.

When you input your first code in line 11 then you will see in Col AF for the code chosen the 1 turining into 0 this mean that you will input values in line 11 for that code and the coresponding code in table 2 to 4 will have a zero effect making the code unique. Etc and etc each code you select in Col Y from row 11 to 23 will be cancelled in the below tables.

Pls check it and let me know if it meets your requirement.
gowflow
Timesheet--2011-Revised.xls
cbumatayController

Author

Commented:
Excellent solution and Thanks so much for deciphering my ill explanation.
cbumatayController

Author

Commented:
ssaqibh:  I tried your solution, and thank you for commenting, but nothing happened.  I select a code and no change to the percentage.  Any suggestions?
Dear cbumatay,
Tks for your grade and comment. I revised the file and made it more efficient especially if you make correction in the old version if you select a code then realise that it is not the correct code and change it it will cancel both codes from table 2 to 4 and that's not good.

This version checks all the codes input in table 1 from row 11 to 23 and only cancel the codes listed no matter what you add or delete codes. Pls give it a try and let me know if it is ok.
gowflow
Timesheet--2011-Revised.xls
cbumatayController

Author

Commented:
Gowflow,
Excellent.  Thanks so much and i've learned a lot from your two submissions/respnses.  Thanks!
Your welcome my pleasure. Pls feel free to post here any other issue you may need help with and I will be glad to assisit you. There is a lot we can do in your file .... as it still hv some discrepancies on other issues (vacation section there is Ref issue and your tables 3 and 4 the headings are wrong etc...) anyway

1 small hickup though, am taking vacation from tomorow and will be back on monday where I will attend again.
gowflow

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial