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

**LJMetzger** · 06-05-2015, 11:53 AM

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.

Sub CreateSheetAndCellDictionary(mySheetAndCellDictionary As Object)
  'This creates a Reference Dictionary of References to a specific Pupil:
  'KEY  = Sheet Name, Column Number in the Sheet Name
  'ITEM = Formula '~~~' in the Formula is replaced by the Row Number for the Pupil on the referenced Sheet
  '
  '
  'For Example if Pupil with Surname 'XYZ' is on row 22 in Sheet 'PIPS'
  'then the '~~~' will be replaced by 22 in the formula
  '
  'The input value is the Row Number on the MASTER SHEET that contains the Pupil to be referenced
    
  'Create the Dictionary Object
  Set mySheetAndCellDictionary = Nothing
  Set mySheetAndCellDictionary = CreateObject("Scripting.Dictionary")
  mySheetAndCellDictionary.CompareMode = vbTextCompare 'case insensitive
          
          
  'Add Values to the Dictionary
  'Formulas on Sheet 'Summative Assessments'
  mySheetAndCellDictionary.Add "Summative Assessments,F", "=PIPS!H~~~"
  mySheetAndCellDictionary.Add "Summative Assessments,G", "=PIPS!I~~~"
  mySheetAndCellDictionary.Add "Summative Assessments,H", "=PIPS!J~~~"
  mySheetAndCellDictionary.Add "Summative Assessments,I", "=PIPS!K~~~"
  mySheetAndCellDictionary.Add "Summative Assessments,J", "=cmy(('InCAS+'!H~~~))"
  mySheetAndCellDictionary.Add "Summative Assessments,K", "=cmy(('InCAS+'!N~~~))"
  mySheetAndCellDictionary.Add "Summative Assessments,L", "=cmy(('InCAS+'!R~~~))"
  mySheetAndCellDictionary.Add "Summative Assessments,M", "=cmy(('InCAS+'!X~~~))"
  mySheetAndCellDictionary.Add "Summative Assessments,N", "=cmy(('InCAS+'!AD~~~))"
  mySheetAndCellDictionary.Add "Summative Assessments,O", "=cmy(('InCAS+'!AI~~~))"
  mySheetAndCellDictionary.Add "Summative Assessments,P", "=cmy(('InCAS+'!AN~~~))"
  mySheetAndCellDictionary.Add "Summative Assessments,Q", "=cmy(('InCAS+'!AS~~~))"
  mySheetAndCellDictionary.Add "Summative Assessments,R", "=cmy(('InCAS+'!AX~~~))"
  mySheetAndCellDictionary.Add "Summative Assessments,S", "=cmy(('InCAS+'!BD~~~))"
  mySheetAndCellDictionary.Add "Summative Assessments,T", "=cmy(('InCAS+'!BI~~~))"
  mySheetAndCellDictionary.Add "Summative Assessments,U", "=cmy(('InCAS+'!BN~~~))"
  mySheetAndCellDictionary.Add "Summative Assessments,V", "=cmy(('InCAS+'!BS~~~))"
  mySheetAndCellDictionary.Add "Summative Assessments,W", "=cmy(('InCAS+'!BX~~~))"
  mySheetAndCellDictionary.Add "Summative Assessments,X", "=cmy(('InCAS+'!CD~~~))"
  mySheetAndCellDictionary.Add "Summative Assessments,Y", "=cmy(('InCAS+'!CI~~~))"
  mySheetAndCellDictionary.Add "Summative Assessments,Z", "=cmy(('InCAS+'!CN~~~))"
  mySheetAndCellDictionary.Add "Summative Assessments,AA", "=cmy(('InCAS+'!CS~~~))"
  mySheetAndCellDictionary.Add "Summative Assessments,AE", "='Big Writing'!H~~~"
  
  'Formulas on Sheet 'Curricular Tracking'
  mySheetAndCellDictionary.Add "Curricular Tracking,E", "=Maths!E~~~"
  mySheetAndCellDictionary.Add "Curricular Tracking,F", "=Maths!K~~~"
  mySheetAndCellDictionary.Add "Curricular Tracking,G", "=Maths!L~~~"
  
  'Formulas on Sheet 'Screening Year on Year'
  mySheetAndCellDictionary.Add "Screening Year on Year,AV", "='Raw Data'!L~~~"
  mySheetAndCellDictionary.Add "Screening Year on Year,AW", "='Raw Data'!W~~~"
  mySheetAndCellDictionary.Add "Screening Year on Year,AX", "='Raw Data'!AI~~~"
  mySheetAndCellDictionary.Add "Screening Year on Year,AY", "='Raw Data'!AT~~~"
  mySheetAndCellDictionary.Add "Screening Year on Year,AZ", "='Raw Data'!BE~~~"
  mySheetAndCellDictionary.Add "Screening Year on Year,BA", "='Raw Data'!BP~~~"
  
  mySheetAndCellDictionary.Add "Screening Year on Year,BB", "='Raw Data'!P~~~"
  mySheetAndCellDictionary.Add "Screening Year on Year,BC", "='Raw Data'!AA~~~"
  mySheetAndCellDictionary.Add "Screening Year on Year,BD", "='Raw Data'!AM~~~"
  mySheetAndCellDictionary.Add "Screening Year on Year,BE", "='Raw Data'!AX~~~"
  mySheetAndCellDictionary.Add "Screening Year on Year,BF", "='Raw Data'!BI~~~"
  mySheetAndCellDictionary.Add "Screening Year on Year,BG", "='Raw Data'!BT~~~"
  
  mySheetAndCellDictionary.Add "Screening Year on Year,BH", "='Raw Data'!H~~~"
  mySheetAndCellDictionary.Add "Screening Year on Year,BI", "='Raw Data'!S~~~"
  mySheetAndCellDictionary.Add "Screening Year on Year,BJ", "='Raw Data'!AE~~~"
  mySheetAndCellDictionary.Add "Screening Year on Year,BK", "='Raw Data'!AP~~~"
  mySheetAndCellDictionary.Add "Screening Year on Year,BL", "='Raw Data'!BA~~~"
  mySheetAndCellDictionary.Add "Screening Year on Year,BM", "='Raw Data'!BL~~~"
  mySheetAndCellDictionary.Add "Screening Year on Year,BN", "='Raw Data'!BW~~~"
  
  
#Const NEED_SHEET_AND_CELL_DICTIONARY_DEBUG_OUTPUT = False
#If NEED_SHEET_AND_CELL_DICTIONARY_DEBUG_OUTPUT Then
  Dim i As Long
  For i = 0 To mySheetAndCellDictionary.Count - 1
    Debug.Print i, mySheetAndCellDictionary.keys()(i), mySheetAndCellDictionary.items()(i)
  Next i
#End If
  
End Sub

Lewis

I was unable to put attachment in this post. Attachment is in post #20 in this thread.