Help with updating names

chris pike
chris pike used Ask the Experts™
on
I have a question that was answered that needs tweaking.
Clear point button only takes whatever data is in Sheet "Carryover" column "F" and only puts it into firs 6 rows in Sheet "Sheet1" "F" … It should fill all rows that have data
It should also leave all data in tact in "Carryover" Column "F" after it pushes data to sheet 1

When names are added to this sheet the formula for sheet 1 "f" should copy down to last name.

We need a way to add names (maybe insert rows) while still maintaining all data for each row name (person)
We always will sort alphabetically
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

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

Author

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

Author

Commented:
Hold on......
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

chris pikeOffice Admin

Author

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

Author

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

Author

Commented:
Sorry WITHOUT updating the entire column values for just that one person
chris pikeOffice Admin

Author

Commented:
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)
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

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

Author

Commented:
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
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

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

Author

Commented:
It’s ok I am away from the office and won’t be able to look at it for a couple days...
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
A preview of coming attractions:)
2019-12-10_13-46-05.mp4
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
If you previously downloaded the workbook, please do it again; I made some minor changes.
chris pikeOffice Admin

Author

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

Author

Commented:
Hi you are here...Coool
Hey I was just checking out the change history. It seems to have died.
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

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

Author

Commented:
Ok... sure, no worries....
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

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

Author

Commented:
Column a or b I think would be best.
I couldn't remember which one it was set up as ….  Sorry
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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'
"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
This should be better. Please see new sheet 'Managers' for the names of the managers that will show up in column 'D' of the 'Change History' sheet.
29166529a.xlsm
chris pikeOffice Admin

Author

Commented:
looking good.... im on my lunch break
chris pikeOffice Admin

Author

Commented:
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...
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Lol, I had already thought of that. New question?
2019-12-12_15-11-32.png
chris pikeOffice Admin

Author

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

Author

Commented:
Perfect Just what the doctor ordered.
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
could we prompt to input date new player started then update sheet 1 column E to todays date.
Column E is 'Total Available'.
chris pikeOffice Admin

Author

Commented:
Column E value  is what that player is entitled to from the first day they start
chris pikeOffice Admin

Author

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

Author

Commented:
Wait never mind
chris pikeOffice Admin

Author

Commented:
OK I am going to try to add those 73 names from carry over to sheet one
chris pikeOffice Admin

Author

Commented:
Wait..... how do I get my 73 names and their values into sheet one? I have them copied into carry over sheet
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

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

Author

Commented:
HI Martin, I am online for one hour this morning. I am going to take a look at the sheet
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I'm here if you have any questions or problems.
chris pikeOffice Admin

Author

Commented:
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
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

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

Author

Commented:
ok will try now
chris pikeOffice Admin

Author

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

Author

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

Author

Commented:
OK seemed to add this time, however I can not seem to update the points
help-update.JPG
chris pikeOffice Admin

Author

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

Author

Commented:
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
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

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

Author

Commented:
OK next step.... as mentioned before We need to get the Column F values into the new sheet.\
chris pikeOffice Admin

Author

Commented:
chris pikeOffice Admin

Author

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

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

Author

Commented:
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
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

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

Author

Commented:
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"
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

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