Exsisting excel sheet to allow value to be update manually before sheets starts adding new data from date columns.
I have a great sheet that adds a new date everyday and pushes data from column "E" into the new date column. All the date columns add up for each person and give a total for each person in the Total Available column "F"
I am trying to start fresh with this sheet. "F" which is the total available, which adds up every new date column, needs to be brought up to date with the previous 2019's numbers.
So what I mean is, If we start fresh there will be one date column... Today....(and of course one added every day) and the Total available will be what ever totals each person already has (from all of 2019) banked.
So if this sheet starts from today, the people will already start with how many points they have banked already. I need to copy and paste those values and then the new date columns will just add to those values.
Final-Safety-Points-2019-Ver_04.xlsm
I am trying to start fresh with this sheet. "F" which is the total available, which adds up every new date column, needs to be brought up to date with the previous 2019's numbers.
So what I mean is, If we start fresh there will be one date column... Today....(and of course one added every day) and the Total available will be what ever totals each person already has (from all of 2019) banked.
So if this sheet starts from today, the people will already start with how many points they have banked already. I need to copy and paste those values and then the new date columns will just add to those values.
Final-Safety-Points-2019-Ver_04.xlsm
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Are you saying that if a person currently has 1000 points earned over a number of days, that you want to press a button that clears all the data columns starting with 'H' and then adds column a new column 'H' for today and then have the 'Total Available' for, say, Arvin be 1001.5?
Let's say Arvin has already 1000 points from last year..... when we start with the new year, January 1st he will have 1001 and jan 2nd he will have 1002 . that clear is just to help us test the sheet for functionality and bring us back to day one.
Now each person will have a different balance of points from last year. I need to be able to plug those number in , and let the sheet continue to add a new date and a new point.
Does that make sense?
Now each person will have a different balance of points from last year. I need to be able to plug those number in , and let the sheet continue to add a new date and a new point.
Does that make sense?
Sorry but it's still not clear to me. There's already a macro in Module1 called 'Clear'. Run it and tell me how it should be changed.
Don't think it matters I will only clear it once a year.
We can just leave it as a manual entry I think
We can just leave it as a manual entry I think
I don't believe in leaving things to be done manually. I could put 1-1-nn there or today's date and if that's not correct you can then change it manually, so please choose one of those dates choices or suggest one of your own.
Try this workbook. I added a 'Carryover' sheet (that could be hidden) . It contains a column for 'Player' and a column for 'Total Available'. That column is currently all 0. I also changed the formulas on Sheet1 column 'F' to add in what's in the 'Carryover' sheet. When you click the 'Clear Points' button a delicate little dance is done.
- The data on Sheet1 starting at column 'H' is deleted
- Sheet1 column 'B' is copied to column 'B' on the 'Carryover' sheet
- The *values* in Sheet1 column 'F' are copied to column 'F' on the 'Carryover' sheet
- "H2" on Sheet1 is set to today
- That column is updated
Ahhhh…. I found something....LOL
When I added all my 73 names.... and hit Set base. it only sets the first 6 rows in column "C"
When I added all my 73 names.... and hit Set base. it only sets the first 6 rows in column "C"
And total available "F" also only updates first 6 rows as well..that happens when you clear points
.
.
.
.
I put in values in Carry over sheet for all 73 names.
Hit clearpoints and only the first 6 values were updated
.
.
.
.
I put in values in Carry over sheet for all 73 names.
Hit clearpoints and only the first 6 values were updated
Can we double check that when I insert a row for a new name to keep them alphabetical it will maintain the integrity of the data/.?
OK, on sheet 1 when we want to clear points I think we need to copy column a and column b
Column B is looking for the full name that is in column A
if we just copy "B" alone I get a #value# error
Column B is looking for the full name that is in column A
if we just copy "B" alone I get a #value# error
I put in values in Carry over sheet for all 73 names.You should not do that. As I mentioned above,
When you click the 'Clear Points' button...Sheet1 column 'B' is copied to column 'B' on the 'Carryover' sheet
Column B is looking for the full name that is in column AI don't think you ever mentioned before that there is data in column 'A'. Does your workbook then have a formula in column 'B' rather than first-name-last-initial text? If so what is that formula?
This corrects the column 'C' problem. I'd like to add new functionality that will allow you to add new names at the click of a button.
29166389a.xlsm
29166389a.xlsm
Premium Content
You need an Expert Office subscription to comment.Start Free Trial