I have a workbook which contains child session information at a Nursery school. There are 3 sheets : Name, Calendar, Sessions.
The Name sheet simply holds a list of children's names and dates.
The Calendar sheet holds the session codes for the children. For example, a code 3 against a child's name on a specific day means the child will be attending the Under 3s class.
The Sessions sheet totals the number of sessions each child attends against a given code for each month.
The problem occurs if I try to manually sort the Child names in alphabetical order in all 3 sheets. When I come to sorting the list in the Sessions sheet the cell references in the array formulas don't change, therefore the numbers calculated refer to the wrong rows.
I tried using the INDIRECT and ROW functions in the FIND section of my array formula so it would pick up the ID value for that row but all it returned was a value of 0.
The example attached is a cutdown version as there is much sensitive information in the original, but the same principle applies to both.
Can someone suggest a Sessions sheet array formula which allows me to sort the data on the Name column of that sheet ?
Hope that is clear.