+ Reply to Thread
Results 1 to 29 of 29

How to add or remove names within one sheet and automatically update others

  1. #1
    Forum Contributor
    Join Date
    11-09-2014
    Location
    Lauder, Scotland
    MS-Off Ver
    Windows 8.1
    Posts
    160

    How to add or remove names within one sheet and automatically update others

    Hi,

    I have created (with a lot of help) a tracking and monitoring spreadsheet for school.

    I have a sheet within it titled 'Add or Remove Names' that the other sheets pull information from.

    How can the spreadsheet be set up so that if I delete a row within this then it will automatically delete the information within the other sheets to do with that particular child.
    Similarly if I was to add a child to this sheet it would automatically update the other sheets with the child info also?

    It may already be able to do this- I am afraid to try out incase I break it!!

    Any thoughts or ideas?

    Thanks,
    Kevin
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: How to add or remove names within one sheet and automatically update others

    Can;t you just add or remove on the Add or Remove Pupil tab?

    It seems everything is linked to that tab for the Names.
    Please ensure you mark your thread as Solved once it is. Click here to see how.
    If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.

  3. #3
    Forum Contributor
    Join Date
    05-10-2011
    Location
    Central Ohio
    MS-Off Ver
    Excel 2000, and 2010
    Posts
    654

    Re: How to add or remove names within one sheet and automatically update others

    I have had some sucess using a 'piviot list' see the example. THen drive all other names in the worksheet from that list.

    Jim O

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: How to add or remove names within one sheet and automatically update others

    Try this. This uses a filter by formula that filters all the names from the add and remove worksheet and enters the names on each of the worksheets that have the names.

    I had to zip the file as it was too large to upload.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  5. #5
    Forum Contributor
    Join Date
    11-09-2014
    Location
    Lauder, Scotland
    MS-Off Ver
    Windows 8.1
    Posts
    160

    Re: How to add or remove names within one sheet and automatically update others

    Hi newdoverman and JO505,
    What is the difference of using your method to what I already had. Would I have encountered problems with my current system for adding and removing names?

  6. #6
    Forum Contributor
    Join Date
    11-09-2014
    Location
    Lauder, Scotland
    MS-Off Ver
    Windows 8.1
    Posts
    160

    Re: How to add or remove names within one sheet and automatically update others

    Hi Newdoverman

    Sorry to be a pain but would you mind doing your magic on this file also. The previous one that I uploaded had some sheets removed as the file was to big to upload- I didnt think about just compressing the file!

    I thought I could just copy the deleted tabs back into the workbook that you adapted for me but it doesn't seem to like this.

    Also would you mind giving me a brief explanation as to the benefits of using your system. Will this interfere with any other formulas that i have within the system?

    Thank you again very much for your support.

    Kevin
    Attached Files Attached Files

  7. #7
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: How to add or remove names within one sheet and automatically update others

    Your formulae referenced specific cells and if those cells are deleted, you end up with a #Ref error.

    I don't think these formulae will cause you problems. I haven't checked very much though.

  8. #8
    Forum Contributor
    Join Date
    11-09-2014
    Location
    Lauder, Scotland
    MS-Off Ver
    Windows 8.1
    Posts
    160

    Re: How to add or remove names within one sheet and automatically update others

    Hi newdoverman,

    I have trialled out this solution although there seems to be a couple of errors. I deleted a row from 'Add or Remove Names' however this is what happens with the other sheets:

    1. it deletes the name, class and date of birth from the other sheets but does not delete the information from the other columns causing all data to be out of sync with the childrens name.
    2. when viewing the 'summative assessment' sheet the following message is displayed- 'Run-time error '13' Type mismatch'

    Anyone have any ideas how to fix this?

  9. #9
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: How to add or remove names within one sheet and automatically update others

    This won't work at all. A VBA solution is the only possible way to have what you want.

    Having had to keep records, I don't like solutions that delete records. It is better to identify their status rather than getting rid of the records. You could do this by going back to the original workbook, add a column for status on each worksheet then you can use the filters to manipulate your records.

    I have done this with your original workbook by adding a Status column and marked a few records with an x (just for convenience as you can have anything that you like) to signify a non-active or if you will "deleted" record.

  10. #10
    Forum Contributor
    Join Date
    11-09-2014
    Location
    Lauder, Scotland
    MS-Off Ver
    Windows 8.1
    Posts
    160

    Re: How to add or remove names within one sheet and automatically update others

    Thanks newdoverman! I may look into possibility of VBA but until then I will just leave pupils on the record.

  11. #11
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: How to add or remove names within one sheet and automatically update others

    Hi Kevin,

    Try the following Macro to REMOVE all traces of a Student from the Workbook.

    Instructions:
    a. You must be on Sheet 'Add or Remove Pupil'.
    b. Select ('Left Click') any cell in the Workbook.
    c. The software will ask you if you REALLY WANT to REMOVE information for the PUPIL on the row selected.
    d. Yes or No will determine that pupil's future.

    I am working on the ADD STUDENT part of your question. It's a lot more complicated than the DELETE, and should take about another week (not full time).


    Please Login or Register  to view this content.
    Lewis

  12. #12
    Forum Contributor
    Join Date
    11-09-2014
    Location
    Lauder, Scotland
    MS-Off Ver
    Windows 8.1
    Posts
    160

    Re: How to add or remove names within one sheet and automatically update others

    Fantastic Lewis!!!

    I will give this a try. As always I so appreciate your ongoing support.

    Thanks massively!

  13. #13
    Forum Contributor
    Join Date
    11-09-2014
    Location
    Lauder, Scotland
    MS-Off Ver
    Windows 8.1
    Posts
    160

    Re: How to add or remove names within one sheet and automatically update others

    Please excuse my ignorance LJMetzger but how do I add this macro to my workbook?

  14. #14
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: How to add or remove names within one sheet and automatically update others

    Hi Kevin,

    I've included the Macro in the attachment to this file.

    Installation Instructions:
    a. Make a Backup copy of your file.
    b. Extract file 'ModAddOrRemoveStudent.bas' from the .zip file (the only file in the .zip file).
    c. Open Your file.
    d. 'Left Click' on any cell in the Excel Spreadsheet.
    e. ALT-F11 to get to VBA.
    f. CTRL-R to get project explorer (if it isn't already showing).
    g. Right click anywhere in 'Project Explorer'.
    h. Select import file. Select file 'ModAddOrRemoveStudent.bas' to import.
    i. Save your file.
    j. Done.


    Instructions for Use (corrected):
    a. You must be on Sheet 'Add or Remove Pupil'.
    b. Select ('Left Click') any cell in the Workbook.
    c. Press 'ALT f8' to get a list of Available Macros.
    d. Select Macro 'RemoveOnePupilFromWorkbook()' and Select RUN.
    e. The software will ask you if you REALLY WANT to REMOVE information for the PUPIL on the row selected.
    f. Yes or No will determine that pupil's future.

    I apologize for your problems.

    Lewis
    Attached Files Attached Files

  15. #15
    Forum Contributor
    Join Date
    11-09-2014
    Location
    Lauder, Scotland
    MS-Off Ver
    Windows 8.1
    Posts
    160

    Re: How to add or remove names within one sheet and automatically update others

    Hi Lewis,
    I have installed the macro and gave it a trial run.
    All seems to work fine except that once a name has been deleted I can no longer search for names within 'Learner Report' (C5)
    Is this fixable?
    Attached Files Attached Files

  16. #16
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: How to add or remove names within one sheet and automatically update others

    Hi Kevin,

    I will fix the 'Learner Report' Problem when I post the 'Add a Student' code.

    In the mean time there seems to be an inconsistency for Forename and Surname on the following Sheets:
    a. Add or Remove Pupil (Surname in Column B, Forename in Column C)
    b. Sort (Composite[Column D] used for sorting has Forename_Surname). This is fine if you want to Sort by Forename, but my speculation is that you want to sort by Surname.
    c. Learner Report cells C6 and C7 have Forename and Surname reversed due to Sort Column D.

    Lewis

  17. #17
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: How to add or remove names within one sheet and automatically update others

    Hi Kevin,

    See the attached .zip file which contains:
    a. ExcelForumStudentData.xls - sample file for use with Excel 2003 ONLY.
    b. ExcelForumStudentData.xlsm - the same sample file for use with Excel 2007 and later Excel versions.
    c. 5 .bas files - Excel VBA modules (see below).

    I finally finished testing and resolved all the known problems I had. Some of the problems I thought I had, seem to be a major design flaw in your worksheet design if almost any sheet is sorted. For example using my sample spreadsheet, if sheet 'PIPS' is sorted, the PIPS scores on Sheet 'Summative Assessments' no longer correspond to the proper pupil (See Pupil 'Mickey Mantle'). I have a macro that can resolve that problem.

    Please do not feel hurried to give me feedback. I understand that the spreadsheet is complex, and that the spreadsheet priority is very low compared to your other duties.

    However, if the Master List of Names (Sheet 'Add or Remove Pupil') is sorted, the data on other sheets is scrambled, and there is no way I know of with the current design, to unscramble the data.

    I would suggest making frequent backup copies of your file, and suggest that sorting is not done.

    The problem can probably be corrected with little or no change to your design, but would probably take a couple of months of intermittent work to implement.

    Please note that the same problem seems to exist in your original file that contains NO VBA code.
    -------------------------------


    This update includes all software I have written for you on various threads. Hopefully, I've solved more problems than I have created. Your workbook design is excellent (except for the ability to scramble the data), but somewhat complex, and I hope I have not overlooked anything. Please let me know if anything is not working as you expected.

    NOTE: Color created using CONDITIONAL FORMATTING CAN NOT be copied to another cell. CONDITIONAL FORMATTING stands alone. To put color in the 'Learner Report' Sheet for data on other sheets that uses CONDITIONAL FORMATTING, you have to add conditional Formatting to the 'Learner Report' Sheet.

    Please let me know if you have any problems or questions.

    Items of Note:
    a. Adding students takes a relatively long amount of time. Progress is displayed on the 'Status Bar'.
    b. I had problems with the formulas on the 'Sort' Sheet being corrupted. I emulated the formulas with VBA. The only columns used on the 'Sort' Sheet are Column 'E' (composite names) and new Column 'F' (reference to the row number on Sheet 'Add or Remove Pupil'.
    c. It seems like the references to 'Surname' and 'Forename' on the 'Learner Sheet' may be reversed. See the fix below if this is the case.
    d. Added OPTIONAL 'Pupil Count' in cell 'F1' of Sheet 'Add or Remove Pupil'.
    e. Spelling Error on Sheet 'Learner Report' cell 'B20' 'Detailled Report' (two Ls)


    To delete a module in the VBA Editor:
    a. 'Left Click' on any cell in the Excel Spreadsheet.
    b. ALT-F11 to get to VBA.
    c. CTRL-R to get project explorer (if it isn't already showing).
    d. 'Right Click' the module to be Deleted.
    e. 'Left Clock' Remove ...
    f. Select 'No' when asked 'Do you want to export ...'.

    To import or export VBA Module Code:
    a. To import, right click anywhere in 'Project Explorer'.
    b. Select import file. Select a file to import.

    Suggestions for testing the code:
    a. Do preliminary testing on my Excel File (ExcelForumStudentData.xlsm ).
    b. If successful, install the software on a copy of your file that contains actual data.
    c. Since your formulas are somewhat complex, verify that pupil data is correct on the sheets that reference pupil data.
    NOTE: Software Development was done using Excel 2003, which may have lost some CONDITIONAL FORMATTING colors, since Excel 2003 only allows 3 CONDITIONAL FORMATS per cell.

    Instructions for installing the code in your file.
    a. Make a backup copy of your file.
    b. Extract the following .bas files from the attached .zip file.
    (1) ModAddOrRemoveStudent.bas
    (2) ModColorCodeSummAssessments.bas
    (3) ModHowAreWeDoing.bas
    (4) Module1.bas
    (5) ModUtilities.bas
    c. Open your file.
    d. Alt F11 - to get to VBA environment.
    e. CTRL R - to open Project Explorer (if not already opened).
    f. In Project Explorer, 'Double Click' on 'Summative Assessments'. Cut and paste the following code into the 'Summative Assessments' module:
    Please Login or Register  to view this content.
    g. In Project Explorer, 'Double Click' on 'Learner Report'. Cut and paste the following code into the 'Learner Report' module:
    Please Login or Register  to view this content.
    g1.In Project Explorer, 'Double Click' on 'How are we doing in Maths'. Cut and paste the following code into the 'How are we doing in Maths' module:
    Please Login or Register  to view this content.

    h. Delete all the modules listed in b. above (in Project Explorer). 'Delete Module' instructions are included above.
    i. Import the modules from b. above (in Project Explorer).
    j. Save your file.
    k. Done.

    Additional Installation Instructions:
    a. Make a backup copy of your file.
    b. Open your file
    c. Add pupil count (optional). On Sheet 'Add or Remove Pupil' Cell 'F1' add the following Formula:
    Please Login or Register  to view this content.
    d. Correct 'Learner Report' Formulas
    (1) On Sheet 'Sort', change formulas in Column 'D' to use column 'B' first:
    Please Login or Register  to view this content.
    (2) On Sheet 'Learner Report', switch the formulas in cells 'C6' and 'C7':
    Please Login or Register  to view this content.
    e. Optional. Assign the following macros to those CommandButtons:
    (1) AddOneOrMorePupilsToWorkbook() - to to 'Add Pupil(s)
    (2) RemoveOnePupilFromWorkbook() - to 'Delete 1 Pupil'.
    (3) CorrectCrossReferenceErrors() - to correct errors if '#REF!' appears in one or more Sheets.
    f. Save your file.
    g. Done.


    Instructions for Use to ADD or DELETE Pupils:

    a. You must be on Sheet 'Add or Remove Pupil'.
    b. Select ('Left Click') any cell in the Workbook.
    c. Press 'ALT f8' to get a list of Available Macros.

    d. Select Macro 'RemoveOnePupilFromWorkbook()' and Select RUN.
    e. The software will ask if you REALLY WANT to REMOVE information for the PUPIL on the row selected.
    f. Yes or No will determine that pupil's future.

    g. Press 'ALT f8' to get a list of Available Macros.
    h. Select Macro 'AddOneOrMorePupilsToWorkbook()' and Select RUN.
    i. The software will ask for the number of Pupils to add. Select 'Cancel' or Press the 'Esc' key to not add any pupils.

    If the workbook appears to be corrupt, running Macro CorrectCrossReferenceErrors() may correct the errors.


    NOTE: If any formulas are added/deleted that reference a sheet other than Sheet 'Add or Remove Pupil', one or more lines MUST BE ADDED/DELETED from Sub CreateSheetAndCellDictionary(). The Sub contains Sheet Names and columns for the formulas, and also contains the formulas. In the formulas '~~~' is used instead of a row number.

    Lewis
    Attached Files Attached Files
    Last edited by LJMetzger; 06-05-2015 at 11:26 AM. Reason: File superseded - see post #19. Additional NEW installation instructions in Blue

  18. #18
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: How to add or remove names within one sheet and automatically update others

    ^^ He should get an award for most comprehensive post I have ever seen on an excel forum EVER ^^

  19. #19
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: How to add or remove names within one sheet and automatically update others

    Hi Kevin,

    This post should address all short term requirements.

    See post #17 in this thread for instructions. New instructions (previously omitted in error) are in blue.

    Other important information:
    Current Rules to maintain data integrity


    Sheet ‘‘Add or Remove Pupil’
    a. DO NOT SORT Sheet ‘Add or Remove Pupil’.
    b. Rows can be MANUALLY moved in Sheet ‘Add or Remove Pupil’ as follows:
    (1) Insert Blank Rows anywhere on the Sheet.
    (2) Cut and paste existing rows to the Blank Rows.
    (3) Delete rows that were the source of ‘Cut and Paste’ to maintain the same number of contiguous rows as were originally on the Sheet.
    (4) After all rows have been moved, run macro CorrectCrossReferenceErrors() to make references on other sheets refer to the correct pupil.
    c. Using AutoFilter to display only certain rows IS ALLOWED.

    Other Sheets
    a. AutoFilter can be used to sort by first sorting by Surname, and then by Stage, both in Ascending order.
    b. After ‘Other Sheets’ have been sorted, run macro CorrectCrossReferenceErrors() to make references on other sheets refer to the correct pupil.
    c. Using AutoFilter to display only certain rows IS ALLOWED.

    The following Macro in Module ModAddOrRemoveStudent (used when attempting to correct formula cross reference errors) must be edited if a formula is Added, Deleted, or Changed.
    Please Login or Register  to view this content.
    Lewis

    I was unable to put attachment in this post. Attachment is in post #20 in this thread.
    Last edited by LJMetzger; 06-05-2015 at 12:09 PM.

  20. #20
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: How to add or remove names within one sheet and automatically update others

    I was unable to put attachment in previous post. Attachment associated with post #19 in this thread is attached to this post.
    Attached Files Attached Files

  21. #21
    Forum Contributor
    Join Date
    11-09-2014
    Location
    Lauder, Scotland
    MS-Off Ver
    Windows 8.1
    Posts
    160

    Re: How to add or remove names within one sheet and automatically update others

    Hi Lewis,

    Thank you. I have now installed your macros and my file is now starting to look a bit more like your sample now. Thank you for the advice.

    I have made some amendments to 'Summative Assessments' though as we no longer need the columns on GL Assessments but most of all because the data seemed to have shifted about slightly.

    This now looks to be fixed however when I click on 'Correct Cross Reference Errors' Macro then the summative assessment sheet goes all to pot again.

    You noted in your last post that if I make changes then I needed to run a macro titled 'ModAddOrRemoveStudent'- however I viewed my macros and could not find this?

    Please can you help me to alter the cross reference macro so that it does not alter my 'summative assessment' sheet.

    (I've still to play around with add or remove names to check if all working correctly).

    Thanks again,
    Kevin

  22. #22
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: How to add or remove names within one sheet and automatically update others

    Hi Kevin,

    It looks like we're very very close to success.

    You noted in your last post that if I make changes then I needed to run a macro titled 'ModAddOrRemoveStudent'- however I viewed my macros and could not find this?
    My instructions were not clear. I meant Sub CreateSheetAndCellDictionary() must be changed in module 'ModAddOrRemoveStudent' when a change is made. See below.


    I have made some amendments to 'Summative Assessments' though as we no longer need the columns on GL Assessments but most of all because the data seemed to have shifted about slightly.

    This now looks to be fixed however when I click on 'Correct Cross Reference Errors' Macro then the summative assessment sheet goes all to pot again.
    I noticed that the file I sent you seemed to be was off one column from the file I sent you. When you do that in Excel without VBA, Excel magically adjusts all the formulas and references.

    With VBA, this is not the case. Each time Excel rows and or columns are changed when using VBA, it is probable that VBA code has to be changed. Typically in this project, changes like that are localized in routines:
    a. CreateSheetAndCellDictionary() - This must be changed each time a column is added, deleted or moved to make sure the formulas are correct. It must also be changed each time a formula is added, deleted, or changed.
    b. PopulateLearnerReportSheet() - This must be changed each time a design change is made to Sheets:
    (1) Add or Remove Pupil
    (2) Pastoral
    (3) Summative Assessments
    (4) Curricular Tracking
    (5) Learner Report

    You should study the two routines when you have time in the future to see how the recipes work. Eventually you will be able to modify them yourself if needed.

    Please can you help me to alter the cross reference macro so that it does not alter my 'summative assessment' sheet.
    Install the .bas files in the attached .zip file in your file as follows:

    Installation Instructions (full installation not required, because the Sheet code has not changed):
    a. Make a backup copy of your file.
    b. Extract the following .bas files from the attached .zip file.
    (1) ModAddOrRemoveStudent.bas
    (2) ModColorCodeSummAssessments.bas
    (3) ModHowAreWeDoing.bas
    (4) Module1.bas
    (5) ModUtilities.bas
    c. Open your file.
    d. Alt F11 - to get to VBA environment.
    e. CTRL R - to open Project Explorer (if not already opened).
    f. Delete all the modules listed in b. above (in Project Explorer). 'Delete Module' instructions are included above.
    g. Import the modules from b. above (in Project Explorer).
    h. Go to Sheet 'Add or Remove Pupil'.
    i. Click on the CommandButton to 'Correct Cross Reference Errors'.
    j. Save your file.
    k. Done.

    Since you are nearing a hard time deadline, now is probably not the time to make design changes (adding/removing columns).

    Lewis

  23. #23
    Forum Contributor
    Join Date
    11-09-2014
    Location
    Lauder, Scotland
    MS-Off Ver
    Windows 8.1
    Posts
    160

    Re: How to add or remove names within one sheet and automatically update others

    Thank you very much Lewis.

    I have installed the modules and all seems to be working great... apart from one tiny part.

    Column AB within 'Summative Assessments is not automatically colour coding like it used to.

    How can I get around this?

    I now understand that I will need to modify the two routines in the future when new columns or formulas have been added, deleted or moved. Do I need to alter the routines if i add, delete or move names within 'Add or Remove Pupils' also?

    Thanks so much,
    Kevin

  24. #24
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: How to add or remove names within one sheet and automatically update others

    Column AB within 'Summative Assessments is not automatically colour coding like it used to.
    Change the following lines in Module ModColorCodeSummAssessments starting at line 30:
    a. Change 'J' to 'K' in 3 places
    b. Change 'AA' to 'AB' in 3 places
    Please Login or Register  to view this content.

    I now understand that I will need to modify the two routines in the future when new columns or formulas have been added, deleted or moved. Do I need to alter the routines if i add, delete or move names within 'Add or Remove Pupils' also?
    No. 'Add or Remove Pupils' should be OK.
    Last edited by LJMetzger; 06-06-2015 at 10:53 AM.

  25. #25
    Forum Contributor
    Join Date
    11-09-2014
    Location
    Lauder, Scotland
    MS-Off Ver
    Windows 8.1
    Posts
    160

    Re: How to add or remove names within one sheet and automatically update others

    Thank You!!!!!!!

    This thread is now complete!! All seems to be working correctly!

    I am so grateful for all your support Lewis!

  26. #26
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: How to add or remove names within one sheet and automatically update others

    Kevin,

    It's nice that you are satisfied. However, I think there may be some subtle problems that neither of us thought of. Please let me know if anything like that shows up.

    In the mean time, I will be thinking about a long term fix to sheet reference problems.

    Lewis

  27. #27
    Forum Contributor
    Join Date
    11-09-2014
    Location
    Lauder, Scotland
    MS-Off Ver
    Windows 8.1
    Posts
    160

    Re: How to add or remove names within one sheet and automatically update others

    Hi Lewis- You were correct, there still seems to be some teething issues.

    The hope is that we can duplicate this tracking and monitoring sheet so that it can be used in other schools.

    I work in two schools so I removed each name from the 'Add or Remove Pupils' sheet using the macro to remove rows one at a time.

    I then set about adding children from the other school. This school is larger. This led to my first problem:
    1. I could only add a maximum of 99 pupils at one time. Is it possible to adapt the system so that schools can add more than 99 pupils at a time. Ideally if this could be set to 999 then that would be great.

    When attempting to add new names I was getting the following problems:
    1. A message popped up to say there was a data integrity issue.
    2. The 'Correct Cross References' button kept duplicating.
    3. Even though I inserted the date of births the spreadsheet did not work out their actual age as before.
    4. When adding new names it creates rows that are very wide- can these remain the original size?

    Lewis has been a huge help to me but to save bothering him again- if anyone else knows how to fix any of the above then I would be very thankful.

    I have attached the spreadsheet so that you can see errors for yourself.

    Kevin
    Attached Files Attached Files

  28. #28
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: How to add or remove names within one sheet and automatically update others

    You always seem to come up with situations I did not anticipate.

    I could only add a maximum of 99 pupils at one time.
    In module ModAddOrRemoveStudent starting at line 155, replace 99 with 999 in 3 places:
    Please Login or Register  to view this content.

    When attempting to add new names I was getting the following problems:
    I did not anticipate that you would start a new workbook from scratch. I always anticipated that there would be at least one student in the file. That is why you are getting the errors.

    There has to be at least one row of existing data remaining in the workbook. When a new pupil is created, the previous row is copied on all sheets, which includes formulas and formatting.

    ----------------------------------

    I know you are excited about how this is working. However, I think a lot more testing is needed, before you let the rest of world loose on the application. I usually required 4 to 12 weeks of testing on a completed application, before releasing completed software. The boss usually won the fight with less testing, and the boss was always sorry. Bosses never seem to learn.

    Lewis

  29. #29
    Forum Contributor
    Join Date
    11-09-2014
    Location
    Lauder, Scotland
    MS-Off Ver
    Windows 8.1
    Posts
    160

    Re: How to add or remove names within one sheet and automatically update others

    Thanks Lewis.

    I will adapt the code to suit. Ta

    I have saved a new blank version of the spreadsheet with dummy name inserted in first row.

    Yeah this is me testing it out before I share it with others. I work in a large school and small school so I am going to test it over the two for a few months to see where the errors lie.

    Thank you again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 10-06-2014, 09:44 AM
  2. [SOLVED] Automatically Update Sheet Names
    By Steve in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-26-2005, 12:05 AM
  3. [SOLVED] Automatically update links when server names change
    By J Hotch in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 11:05 AM
  4. [SOLVED] Automatically update links when server names change
    By J Hotch in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 07:05 AM
  5. Automatically update links when server names change
    By J Hotch in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 06:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1