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]

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.

Will you still need the previous weeks numbers tallied into the present week numbers J:P plus the new column ?

I presume the new column will be the one to lock ?

 

[Logit]

After thinking on this further, is there a way you could leave columns J:P as they are and not add more columns ? You would change the dates in the header of those
columns and erase the data in those rows. Is that doable ? If you need to retain the data in those columns/rows ... we could copy/paste it to another sheet and add
new data to that other sheet as you progress.

 

[Chilliflake]

After thinking on this further, is there a way you could leave columns J:P as they are and not add more columns ? You would change the dates in the header of those
columns and erase the data in those rows. Is that doable ? If you need to retain the data in those columns/rows ... we could copy/paste it to another sheet and add
new data to that other sheet as you progress.

Hi, that's a good idea as it could become unmanageable otherwise. Maybe five columns only which would cover any months that have five weeks and leave it at that, just enough to cover a month and then they can be archived.

The column to lock would be column P from the Daily Errors side and all of the columns in the Weekly Errors side, as they generate results only, with no need for user input.

 

[Logit]

You need to be more precise with your instructions. Column P from the Daily Errors side .... Daily Errors side doesn't exist on the headers.
There are Daily Totals and Weekly Totals. I believe you intended for Daily Errors side = Daily Totals. If that is the case, Daily Totals doesn't have
a column P.

Did you mean Column G in the Daily Totals side ? That can be done.
I still don't understand why you would want a DATE total (currently column P) locked. I understand column Q but not P. Please explain your goal.

 

[Chilliflake]

You need to be more precise with your instructions. Column P from the Daily Errors side .... Daily Errors side doesn't exist on the headers.
There are Daily Totals and Weekly Totals. I believe you intended for Daily Errors side = Daily Totals. If that is the case, Daily Totals doesn't have
a column P.

Did you mean Column G in the Daily Totals side ? That can be done.
I still don't understand why you would want a DATE total (currently column P) locked. I understand column Q but not P. Please explain your goal.

Apologies, I was tired last night when I replied and going from visual memory rather than actually looking at the sheet. Yes, I meant Column G in the Daily Totals side and Column Q on the Weekly Totals side.

 

[Chilliflake]

Apologies, I was tired last night when I replied and going from visual memory rather than actually looking at the sheet. Yes, I meant Column G in the Daily Totals side and Column Q on the Weekly Totals side.

Hi again, have just received an email from my manager requesting a column/s that will allow the user to enter the source of each error! It will be one of three departments – NPT, Control, or Front Office.

I think this would be too messy for a macro. It might be something that would need to be entered manually somewhere...what do you think?

The email also notified me that Area Manager is meeting with the Regional Management Team next week and he (my AM) wishes to update the Management Team on the table.. My anxiety is building!

 

[Logit]

Here is the final (hopefully) version.

To protect the worksheet from prying eyes and prevent unnecessary mistakes, most of the worksheet has been protected with a password.
Presently the password is 123. Not very inventive I know but it is a start. You can change the password by entering the VBE and editing the
code or if you like choose a password of your liking and I'll do it for you.

As you will notice, the menu bar at the top of the workbook has been hidden. This is to prevent those pesky people from being able to make
major changes to the worksheet. There is a button that allows you access to the menu bar with the application of the password.

Any changes that are made to the workbook, whether it be by an employee or by you, will be auto saved when the workbook is closed.

Let me know your thoughts.

Download workbook :

Internxt Drive – Private & Secure Cloud Storage

 

[Chilliflake]

Here is the final (hopefully) version.

To protect the worksheet from prying eyes and prevent unnecessary mistakes, most of the worksheet has been protected with a password.
Presently the password is 123. Not very inventive I know but it is a start. You can change the password by entering the VBE and editing the
code or if you like choose a password of your liking and I'll do it for you.

As you will notice, the menu bar at the top of the workbook has been hidden. This is to prevent those pesky people from being able to make
major changes to the worksheet. There is a button that allows you access to the menu bar with the application of the password.

Any changes that are made to the workbook, whether it be by an employee or by you, will be auto saved when the workbook is closed.

Let me know your thoughts.

Download workbook :

Internxt Drive – Private & Secure Cloud Storage

Hi, apologies, we had a holiday weekend here and finished up early on Friday. The email wasn't released by the IT Dept in time for my meeting, but I went through the basics of it using my original sheet with no macro. I've just managed to get to it now to check it out before work tomorrow. It looks good, thank you, although I have to play around with it for a bit just to see how it all works.

Thanks and will keep you updated. Is there any way to lock the formula in Column G (from 4 down) as I know people will delete it accidentally when they're filling in their daily totals....

Thanks again for your time, you are a fantastic person.

 

[Logit]

My apologies. I managed to give you a download link to the wrong file. My mistake and I am sorry.

Here is the correct file : Internxt Drive – Private & Secure Cloud Storage

If for some reason after downloading and you open the workbook but it asks you to save it ... just cancel that computer request. This happened to me the first time I downloaded the
file and opened it. Not certain why Excel would do that. Strange.

 

[Logit]

I made another few small changes to the workbook ... so ... here is another link to download the latest version ...

Internxt Drive – Private & Secure Cloud Storage