Help with updating names

In the other thread you mentioned that you wanted the base rate to show as dollars. Does that also apply to all the other columns starting at column 'C'?

There is no $$ anywhere . these are only points. I was showing that the column should be formatted for all Number format only

Hold on......

I am getting very confused on the  last fix we did with bringing over data from carry over
 Perhaps I can try to explain what I think should happen from the beginning of setting our new sheet with last years left over values.

OK... here we go....LOL

I have an exsisting sheet that has 73 names in column A and B
Column B shortens the name from first and last to First Last Initial      =LEFT(A3,FIND(" ",A3)+1)

On my sheet that we are currently using that has my current data. I have a column that has a Total Available "F" just like our new sheet
I just want to copy my data from the old column F  from my exsiting sheet into our new sheet. Our new sheet has a formula in those cells in column F

I do however need to be able to manually update a value here or there if there is special reasons to adjust  the total available with updating the entire column values for just that one person.

Sorry WITHOUT updating the entire column values for just that one person

When I copy all my old data from my old sheet to  this new sheet. This will be done only one time. At the end of the year, to start fresh for a new year.

I will need to make sure that the NEW workbook is prepared by having only the one column and it should be today's dates (today)

First I should mention that when I created the Carryover sheet I manually copied the names from Sheet1 column B to the new sheet. I also manually made the Total Available for the Carryover sheet zero.

Let me detail once again what happens with the current workbook when 'Clear Points' is clicked.

1. All the data in the columns starting with 'H' is deleted
2. Then the names in column 'B' are copied to the Carryover sheet
3. Then the values (not the formulas) from column 'F' are copied to the Carryover sheet
4. H2 on Sheet1 is set to today and that column is updated

Note that after the process that the data in Sheet1 column 'F' now shows the former Total plus the new value in column 'H' because the formula in column 'F' refers in part to the total on the Carryover sheet.


Based on that, and if I understand what you mean when you say "manually update a value here or there" you could do that before you click the 'Clear Points' by updating values in the Carryover sheet.

I will test it again, for some reason I can not make it work that way when I add my 73 names.

Your step 2. above should be Column A and B as in the carryover sheet wont have the first and last names, and if sheet names are adjusted or names added/deleted A and B will not match. Sheet one is where we will insert a row to add a new person. So when we copy column B alone it will not work as b has that formula to take first last and make it first last initial.

let me try again and try to show you what happens to me on my side when I try.
Thanks Martin

I will test it again, for some reason I can not make it work that way when I add my 73 names.

Don't try with the current version. I'm going to make it easy.

Your step 2. above should be Column A and B as in the carryover sheet wont have the first and last names, and if sheet names are adjusted or names added/deleted A and B will not match. Sheet one is where we will insert a row to add a new person. So when we copy column B alone it will not work as b has that formula to take first last and make it first last initial.

Currently column 'B' on the Carryover sheet isn't even necessary since when the data comparison between the two sheets is done positionally, rather than by name. So if you manually add a new name in Sheet1 there's no need to worry about what's in Carryover, and when doing 'Clear Points' what is in Carryover is totally deleted and replaced by what's in Sheet1.

Sorry this took so long, I was correcting a problem that caused the workbook to take a long time to load and also fixing a circular reference warning that appeared on workbook open.

Click the button on the 'New Players' sheet.
29166529.xlsm

It’s ok I am away from the office and won’t be able to look at it for a couple days...

A preview of coming attractions:)
2019-12-10_13-46-05.mp4

If you previously downloaded the workbook, please do it again; I made some minor changes.

Hi Martin,
Love the new concept... I think we need to look at the code. It is doing something weird with the data.
Have a look. Top one is after the "add new" bottom is before.

Hi you are here...Coool
Hey I was just checking out the change history. It seems to have died.

I just tested and you're right, there is a problem. When I created this a few days ago it seemed to be working correctly so it is probably somehow do to the new days added when the workbook was opened today. I'll be back.

Ok... sure, no worries....

How do you want to trigger the change history? By double-clicking:

• Column 'A'
• Column 'B' (which is what the workbook currently expects)
• Columns 'A' or 'B'
• Any column

Column a or b I think would be best.
I couldn't remember which one it was set up as ….  Sorry

I couldn't remember which one it was set up as ….  Sorry

No problem. It was originally set up as 'B' because at the time my workbook didn't have any data in column 'A'

looking good.... im on my lunch break

noy a biggy but if name already exsists in sheet 1 and if you add the range of name without clearing out the names first. it will duplicate the rows. Like I said not a biggy.

I am going to tr y to add 73 names to this one...

Lol, I had already thought of that. New question?

Hmmmm thought...…
Seeing how the sheet will always be up to date, and we will only be adding one or two new players at a time, could we prompt to input date new player started then update sheet 1 column E to todays date.

As the user of the sheet opens the sheet say on the 15th of the month. A new player started on the 10th. And he is added to the roster on the 15th when the user gets around to updating the data. This will give the user the ability to add those 5 days missing for the new player

Perfect Just what the doctor ordered.

could we prompt to input date new player started then update sheet 1 column E to todays date.

Column E is 'Total Available'.

Column E value  is what that player is entitled to from the first day they start

When I copy paste my 73 names into column a and b in carry over sheet I loose the double click function

Wait never mind

OK I am going to try to add those 73 names from carry over to sheet one

Wait..... how do I get my 73 names and their values into sheet one? I have them copied into carry over sheet

First never copy anything to the Carryover sheet since that is done automatically.

To add your 73 names just copy/paste them to the New Players sheet starting at row 2, making sure that for now at least they don't already exist in Sheet1.

I say "for now" because I don't remember you saying what you want to do about this suggestion of mine.

HI Martin, I am online for one hour this morning. I am going to take a look at the sheet

I'm here if you have any questions or problems.

Oh boy.... I am back . I ended up only getting 15 minutes yesterday. I am back and HOPEFULLY will get a good chuncjk of time today to work on computer projects. Testing the sheet now

In preparation for working with you today I opened the workbook and was surprised when I saw that the automatic addition of days to Sheet1 didn't happen. To correct that problem,
1) go to Visual Basic
2) open the Immediate Window via Ctrl+G
3) type application.EnableEvents = True in that window
4) press Return
5) Close and save the workbook
6) Reopen the workbook

ok will try now

OK I did that.
I am going to try to ad dthe names again as you instructed above.... I tried once but it kind of exploded, let's see what happens

Small issue I think.. Sheet one ( main sheet) names start at a3
When bnames are added from ne players it drops them into a2  Row 2 is the column header names

OK seemed to add this time, however I can not seem to update the points

When I select update points and select Jan 12th  nothing happens
And when I save the file close and open , it does not auto update either

Ok I have the names in now.... Not really worried about how the yget there, because it is only once a year I will be clearing this out and starting fresh... So I have the name in. That is good

I'm not sure what you are trying to test. When you say Jan 12th do you mean 2020? And when you close and reopen the workbook, what is the last date when you close it?

OK next step.... as mentioned before We need to get the Column F values into the new sheet.\

Here is my sheet
Final-Points-2019-Ver_08_1.xlsm

Hold that here it is again with a sheet "all the old data"

See sheet "old data"
Final-Points-2019-Ver_08_2.xlsm

That is the values and the redeemed values.... no formulas in this sheet only text..
I am being called into production...wil be back... I hope in a couple hours
Thanks so much

The basic problem we are having is that I didn't write this with the idea in mind that there would ever be no players and that seems to be what you are testing. Would it be acceptable to you to start from the workbook that had a few names (or at least the first name)  and add new players to that? If so then to test adding new players to the existing ones you either need to remove the existing ones from your list of 73, or we need to deal with the duplicate player issue that I mentioned a couple of times above.

No we can not remove exsisiting names and their data everytime we want to add names. as they will have a years worth of data along side with their names.

1. First thing is, we want our 73 names in the list ( they are there now) .. all I want to be able to do is add a new player or two new players, and have the main list of 73 name not messed up.

2. I want the sheet to refresh on open from last inputted value in E... I think we have that already

3. I want to be able to update the points FROM a certain date (this is incase there has been a change in point values since last open, or one person has a change in point value that needs to be update.) we have this now with the calendar that pops up.

4. I want to be able to add my last year "total available and redeemed" ( I created a sample sheet in the last uploaded workbook for you) into this new sheet easily. This will only be done once, once it is in then we are good to go. If we can just delete F and G from our new Sheet 1 then just paste the old values from last year into F and G without messing up the calculation for the entire row of each player, then that would be ideal.

After this is all working then I think I have just one more Question and this will be done.
I want to be able to select ONE name and update the point value from "E" from a certain date.. ( I think I mentioned this as a possible additional question).  So If a mistake happens with data and points, we will be able to just select the name with the error , and the date where the error happened and it will update from the given date to present with "E"

No we can not remove exsisiting names and their data everytime we want to add names

No that's not at all what I'm suggesting. but rather that we start with a workbook like the one you originally posted  that already has a few names and that we add to those names, and every time there's a new player we just add that player.

1. First thing is, we want our 73 names in the list ( they are there now) .. all I want to be able to do is add a new player or two new players, and have the main list of 73 name not messed up.

It will do that as I've said above.

2. I want the sheet to refresh on open from last inputted value in E... I think we have that already

Yes we do.

3. I want to be able to update the points FROM a certain date (this is incase there has been a change in point values since last open, or one person has a change in point value that needs to be update.) we have this now with the calendar that pops up.

I agree.

4. I want to be able to add my last year "total available and redeemed" ( I created a sample sheet in the last uploaded workbook for you) into this new sheet easily. This will only be done once, once it is in then we are good to go. If we can just delete F and G from our new Sheet 1 then just paste the old values from last year into F and G without messing up the calculation for the entire row of each player, then that would be ideal.

The Clear Points button should already do that with having to delete F and G.

Please also see my two posts above, but here's an updated workbook. I suggest you

1. Add a few names (that don't already exist) in the New Players list and click Update.
2. If that works then try to add a name that already exists.
3. If that works then try to add "Arvin Good"

If those work to your satisfaction then we can talk about how to test the carryover.
29166529b.xlsm

In addition to the now 3 posts above, here's an explanation of how carryover is designed to work.

The formula in column 'F' of Sheet1 positionally refers to the data from the Carryover sheet's column'F' as part of the total. What I mean by "positionally" is that Sheet1 F3 refers to Carryover F3,  Sheet1 F4 refers to Carryover F4, etc. Because of that, the names on the Carryover sheet aren't really necessary but they are good documentation.  Initially the values on the Carryover sheet has no names and a bunch of zeros in column 'F'.

When the 'Clear Points' button is clicked:

1. All the data to the right of column 'G' is cleared.
2. The values in Sheet1's columns 'B' and 'F" are copied to the Carryover sheet
3. H2 on Sheet1 is given todays date
4. The cells in Sheet1's column 'H" are given column 'E's value. Note that even though there is now only one day's worth of data, column 'F' is incremented rather than replaced because as I mentioned, it also refers to the data in the Carryover sheet which we just updated.

The same thing will happen next time