Exsisting excel sheet to allow value to be update manually before sheets starts adding new data from date columns.

chris pike
chris pike used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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?
chris pikeOffice Admin

Author

Commented:
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?
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.
chris pikeOffice Admin

Author

Commented:
The clear run is just fine. Don't Change it.
Here we go
carryover.mp4
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
When you clear, should H2 say 1-1-nn, where 'nn' is always the current 2-digit year?
chris pikeOffice Admin

Author

Commented:
Don't think it matters I will only clear it once a year.
We can just leave  it as a manual entry I think
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.
chris pikeOffice Admin

Author

Commented:
LOL, I understand..... OK
Can you put Todays date
Thanks
See how it works
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.
  1. The data on Sheet1 starting at column 'H' is deleted
  2. Sheet1 column 'B' is copied to column 'B' on the 'Carryover' sheet
  3. The *values* in Sheet1 column 'F' are copied to column 'F' on the 'Carryover' sheet
  4. "H2" on Sheet1 is set to today
  5. That column is updated
29166389.xlsm
chris pikeOffice Admin

Author

Commented:
OK at work... let's give this shot
chris pikeOffice Admin

Author

Commented:
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"
chris pikeOffice Admin

Author

Commented:
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
chris pikeOffice Admin

Author

Commented:
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/.?
chris pikeOffice Admin

Author

Commented:
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
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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 A
I 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?
"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
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
chris pikeOffice Admin

Author

Commented:
Could we format C as numbers first
format
chris pikeOffice Admin

Author

Commented:
And F should also auto fill formula from above I think
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Yes, in a new question I can do all that. In that question please supply the formula for column 'B'.
chris pikeOffice Admin

Author

Commented:
doing right now
chris pikeOffice Admin

Author

Commented:
Awesome Work as usual.
Thanks MArtin

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial