Freezing the values in some cells whilst altering others

I have a spreadsheet in which a particular employee’s work hours are entered in cells C4:C40 next to the particular job they are working on; and the particular job is selected in drop down boxes in each of cells B4:B40. These hours are summed against each job, in column F (F4:F13), by means of a SUMIF formula. That all works OK.
I want to then put these hours (those in F4:F13) into a table (L4:N23), such that when the employee’s total hours during the week (shown as a summation in cell C41) are less than 40, each of the hours against each of the jobs is copied forward into cells N4, N6, N8, N10, N12, N14, N16, N18, N20 and N22 (basically this means ‘normal hours’); but, when the value in cell C41 is greater than 40, any ADDITIONAL hours (but not the hours already in the even-numbered N-cells) against the various jobs is put into cells N5, N7, N9, N11, N13, N15, N17, N19, N21 and N23 (these are then ‘overtime hours’). But all I can do at the moment, when C41>40, is to make everything, everything, go into the odd-numbered N-cells. I want, effectively, for the values that end up in the even-numbered N-cells when C41<=40 to remain there, frozen, whilst all the additional hours go into the odd-numbered cells.
How can I do this with a formula? Or does it absolutely mean using VBA?
I attach the spreadsheet.
Sample-2-for-EE---151114.xlsx
BlosMusicAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
You can Format Cells->Protection->UnLock the cells you want to be editable ahead of time and then protect the sheet.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you would need to use VBA so you can dynamically set the LOCKED property of cells to true.  By default, they are but you can manually uncheck Locked for cells you want the user to change.  In order for Locked to be respected, you also need to protect the worksheet.  You would not need to do this with a password, but it would need to be done.

When a worksheet is protected, all cells where LOCKED is false can be modified.  When you protect a sheet, there are options of what you want to protect such as Locked cells, inserting rows and columns, formatting, ...

Protect Sheet is on the REVIEW ribbon.  When you click it, you will see a bunch of checkboxes.  This can be done programmatically or manually.

LOCKED is a checkbox on the Protection tab of Format Cells.  This can also be done programmatically or manually.
0
BlosMusicAuthor Commented:
But I want this to work automatically. I want the data input clerk to just put in the employee's hours, and for the spreadsheet to then work out for itself that the hours >40 are overtime, and put these extra hours into the appropriate cell without changing what is already in the 'normal hours' box. Also, I'm not yet competent in VBA, and wondered if there is a formula to allow this to happen - a kind of [=FIX] or [=DON'T ALTER THIS BUT DO WHAT YOU LIKE IN ANOTHER CELL] kind of formula.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Martin LissOlder than dirtCommented:
Are you saying that your requirement is that a cell should be editable until something is manually entered into it and then locked?
0
BlosMusicAuthor Commented:
Yes.
I want the spreadsheet to put all hours into the even-numbered cells in column N against each job, until C41 is greater than 40, and then I want the spreadsheet to put any other hours thereafter into the odd-numbered cells in column N, without changing what's already in the even-numbered N-cells.
0
Martin LissOlder than dirtCommented:
OK I guess you aren't talking about protecting the sheet at all but just about where the data goes. Given the workbook you posted, please tell me the specific cell in which I should enter a new value, or change the value, what the value should be, and specifically what other cells should change and what they should change to.
1
BlosMusicAuthor Commented:
When C41 goes above 40, meaning the employee has now worked over 40 hours in this particular week, I want any new entries in the C column against a job (say you go to cell C23 and add 5 hours against Job2 (selected from the drop down box in cell B23), which will bring the employee’s hours above 40 hours) to be allocated not into the even-numbered cells in column N, but the odd-numbered ones – in this case, since you will have added hours against Job 2, the value of 5 should go into cell N7  – then, because the total hours for this employee for the week is >40, any new hours, such as those you will have just entered, must go into these odd-numbered cells, but without changing what’s already gone into the even-numbered cells.
In essence, I am trying to put any hours worked by an employee against the respective jobs he’s worked on into ‘normal hours (i.e. Type A (<=40)), until his total working hours are >40, at which time any new work he does, against any job, is put into the Type B (>40) cells. But of course, I want to retain in the Type A (<=40) cells all the work he has done up to his 40 hours, so we can pay him his normal hours and his overtime (as one transaction for him, as it happens), but we can then see what hours of what type have been expended against each job, which we need for internal costing purposes.
0
Ejgil HedegaardCommented:
Check if attached file do what you want.
Column S set the type to be A until 40 hours, and after that it is type B.
Column T are the A hours, and column U are the B hours.
In row 22 some of the hours are A, and some are B.
Result for the jobs in columns H and I.
Sample-2-for-EE---151114.xlsx
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BlosMusicAuthor Commented:
As far as I can see, that's exactly what I want!
I will work through it tomorrow, but it seems exactly right!
Thank you.
0
BlosMusicAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for BlosMusic's comment #a41247205

for the following reason:

Quick and concise. Thank you.
0
Martin LissOlder than dirtCommented:
I think the author meant to accept post ID: 41247084.
0
BlosMusicAuthor Commented:
Sorry, I don't understand!
0
Martin LissOlder than dirtCommented:
When you chose the answer that solved your problem you chose your own post rather than this one

Check if attached file do what you want.
Column S set the type to be A until 40 hours, and after that it is type B.
Column T are the A hours, and column U are the B hours.
In row 22 some of the hours are A, and some are B.
Result for the jobs in columns H and I.

from Ejgil Hedegaard which, I assume, is the one you meant to choose.
1
BlosMusicAuthor Commented:
I've done my best, but can find no "accept multiple solutions" choice. Both Martin Liss and Ejgil Hedegaard were extremely helpful, and I want to award them both points, but don't know how to, now that I have posted to the wrong ID. It seems a bit opaque.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you can mark their posts as assists and award points to the assists instead of the solution ~ then when you figure out exactly how that works, perhaps you can post back so we can all know?  

I only know it is possible because it happened to me on another post (getting points for assisting but not solving)
0
BlosMusicAuthor Commented:
Both experts that I have awarded points to have been very helpful. Sorry I didn't manage to sort this out earlier.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.