Adding weekly totals from one cell to an overall total

[Chilliflake]

Hi everybody,

I am creating a League Table for recurring issues in work. It's almost finished, but I don't know how to add the "total weekly figures" from its cell to the destination cell on the main sheet, "overall total", without creating a new weekly column for each week's data.

Is it possible to have a "weekly figures" sheet that is cleared every week, but the figures entered from the "total weekly figures" cell the previous week are not deleted from the destination "overall total" cell, but the new week's figures are instead added to it as a running total, as the people who will be entering weekly figures are not overly Excel-savvy and just want to enter figures into a simple weekly sheet.

Thank you for any help you can offer, have hit a wall as this is way beyond my Excel capabilities.

CF

 

[Logit]

The green colored lines are just remarks for whoever reads the code. Sometimes the remark is an explanation of what that portion of code does ... and sometimes
the remark is directed to the programmer and it gives instructions what action/s to take on that specific section of code.

Not certain why it isn't working there for you. The last copy you posted for download works here. ???

 

[Chilliflake]

Do I leave in the green text or are they instructions for me? I've changed Sheet A to MSQs and run the macro but nothing is happening... Sheet B will be a copy of Sheet A for a different department so that can be ignored for the moment.

The green colored lines are just remarks for whoever reads the code. Sometimes the remark is an explanation of what that portion of code does ... and sometimes
the remark is directed to the programmer and it gives instructions what action/s to take on that specific section of code.

Not certain why it isn't working there for you. The last copy you posted for download works here. ???

Okay I’ll try it again and let you know. Thank you

 

[Chilliflake]

Hi, I’ve ran it again and it’s not working for me.

Here’s what I have:

Dropbox

www.dropbox.com

 

[Logit]

I overlook column Q ... I believe this is what you are referring to. Let me give that some attention and see what I can come up with.

 

[Chilliflake]

I overlook column Q ... I believe this is what you are referring to. Let me give that some attention and see what I can come up with.

Yes, column Q is the one I need.

I also need to be able to recreate the formula in P4 (which will need to be replicated down the length of that column) in each new weekly column that is added to the left of P, without affecting the previous week's column.

Also, I will be changing the names of the Error Types in Columns A and I to actual error names - will that affect the VB code?

 

[Logit]

Changing the error names column A & I will not affect the code.

P4 doesn't have a formula in the last workbook you posted (or any of the workbooks for that matter).

Here is a link to version #5 which does work here, including column Q.

Let me know your thoughts.

Internxt Drive – Private & Secure Cloud Storage

 

[Chilliflake]

Hi, okay, it's getting there thank you. There are a couple of things re the sheet I would like your further input on please.

In the column P4 and down, this figure stays there when the following week's figures are entered into Mon - Friday and updates to only when the macro button is pressed - is that the only way the macro will work or can it be changed to clear when the Mon-Fri figures are cleared each week (without affecting the data in the weekly table)? If it has to stay as is that is fine, but I need the cells from P4 down to be locked - is that possible? I know that people will put figures in quickly from Mon-Fri and will end up going into P4 and accidentally deleting the data there..

Actually, is there a way to lock everything except the user input cells??

I now need to do the following (if you don't mind that is!):

The CWT column needs to link to the ranking section of the sheet - i.e., R4 to X4, as the CWT is what will form the basis of the ranking. The rankings should change as the CWT changes. Once they are provisionally ranked in R4, a differential is added to the ranking to give a new ranking figure (T4) which is then translated to the New Ranking for that error (U4). I might have made a mistake in creating V4 as it could mean the same result as U4?

Finally, the New Ranking should correlate to the correct Error Type.

Is this doable?

I can't thank you enough for your assistance.

Attaching Draft 6 below fyi

 

[Chilliflake]

Dropbox

www.dropbox.com

 

[Logit]

Column P represents the data for the current week. Is there a reason you want that data erased when the weekly data columns B:G are erased ?
I understood column Q is an ongoing sum of all the weeks totals J:P . Is that correct ?
Also, as a different approach, will you be erasing the data in columns J:P when a new month begins so the new dates J3:JP can be accommodated ? Or,
are you planning to add to the dates with new, additional columns ? That will require a revamping of the macro code.

Guess I need further detailed description of how things will work as the weeks progress.

 

[Chilliflake]

Column P represents the data for the current week. Is there a reason you want that data erased when the weekly data columns B:G are erased ?
I understood column Q is an ongoing sum of all the weeks totals J:P . Is that correct ?
Also, as a different approach, will you be erasing the data in columns J:P when a new month begins so the new dates J3:JP can be accommodated ? Or,
are you planning to add to the dates with new, additional columns ? That will require a revamping of the macro code.

Guess I need further detailed description of how things will work as the weeks progress.

There is no reason I need the data in Column P erased when B:G are erased, other than to reduce any confusion that might be caused to the end users by having the previous week's figures remaining. That's why I thought it would be a good idea to lock any editing capabilities in Column P by people other than "admin".

Column Q is an ongoing sum of all the weeks totals J:P that is correct

Re the J:P columns - I think I will need a new column every week, as new figures come in every Monday and reports are run on them by the Friday of each week. I know that management will look for weekly if I haven't done it that way, so am trying to pre-empt their request.

The main thing they want at the moment is a ranking system to be set up...

Thank you