Replies

I came up with an alternate way to do it using code (2003)
It makes some assumptions about the data (ie only ever 5 double columns, and 11 rows each, but they could really be automated)
Forgive the messy code, I’m no expert, but it seems to work ok
It was fun to play with on a rainy Sunday arvo anyway

Hey Snapon2,
If I understand your question correctly, there are two things that you want to achieve, presumably without changing the layout of your current recording sheet too much…..

Firstly, you want to be able to enter each allocation of hours for a specific client into a cell (on a daily or otherwise basis), and have it automatically perform a calculation of the running total of hours, without having to perform that calculation yourself (and presumably not have to have numerous additional columns to hold that daily data, the number of which will vary dependent of the number of separate allocations of work to that client, and the time taken to complete the task?)

Secondly, based on that running sub total, you would like a conditional format to identify where this figure has exceeded the hrs quoted allotment

Assuming I have understood all that correctly, what you are asking for can be achieved, but with the addition of only one more column. I don’t know of any other way that excel can perform this task, without at least one more column, however there are many better skilled minds on this site than mine who may disagree and hopefully show me where I may be wrong.

As per the attached, the additional column will accept the hours worked for the day, add it to the running sub total figure, and then delete itself ready for the next allocation of time to that client. The reason for the additional column, is that I don’t believe that a cell is able to produce the result you are looking for (ie contain an existing value of A, have that value overriden by a daily value of B, and produce a result of A+B in that same cell without the calculation suggested by euHodos in point 1) above.

In saying all of this however, I can see that you may also be including an inherent flaw into the methodology, in that once the hours are entered, there is no way to identify whether you did indeed update them or not (ie it’s possible to forget for example). To that end, I created another column which will automatically enter the time (and date if you want, however I formatted the cell just to show the time only) that the last change was made. This can be deleted each day (which is why it is formatted to capture time only), or can be left in perpetuity and reformatted to show the date and time. It can, of course, be removed altogether from the code should you not want that functionality to be applied. Be aware though, that should you want it to remain, your existing data capture sheet will need to have another blank column inserted as column F, so that it doesn’t overwrite information that you may already have in that column.

I hope I’ve understood your problem, and I hope this helps

Hey Theo,
I think I have understood your predicament, and hope I have an somewhat easy fix

Rather than write some extravagant code for this, I have simply used a dynamic range name for the actual data, and added some buttons which will change it up and down as you wish….if that makes sense. All you will have to do when you copy and paste the new data, is to hit the button to add data. For your ease, I also added a button to automatically reduce the data by one wk as well, just in case you hit the increase twice, or just wanna play with it

These changes can be found using the INSERT – NAME – DEFINE menu items for the dynamic range names, and within the source data area of the chart itself

The VBA code comoponent (ALT + F11) is really simple (and short) and references cell range A5 (for the purpose of the exercise only, and can be changed to any other cell as needed)

The only real problem will be setting it up for all of the graphs, but once done, clicking the button will change all graphs at the same time

Hope this helps you out, or gives you a better idea