asked on

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

Martin Liss

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 pike

ASKER

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

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 pike

ASKER

The clear run is just fine. Don't Change it.
Here we go
carryover.mp4

Martin Liss

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

chris pike

ASKER

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

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 pike

ASKER

LOL, I understand..... OK
Can you put Todays date
Thanks
See how it works

Martin Liss

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

29166389.xlsm

chris pike

ASKER

OK at work... let's give this shot

chris pike

ASKER

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 pike

ASKER

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 pike

ASKER

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 pike

ASKER

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

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?

ASKER CERTIFIED SOLUTION

Martin Liss

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

chris pike

ASKER

Could we format C as numbers first