Ok - after 2 hours of drilling my brain out over formulae I have finished the VO Summary page for our current tracker. This will automatically total how many applications we have from each visa office, and other useful information such as how many PERs, 2nd AOR and Med Requests and also what % of these have been received in comparison to the total applications.
This will make the following assumptions:
- That the visa offices are correctly spelt (case sensitive) in the tracker
- That the PER, 2nd AOR and Med Request dates are correctly filled in on the tracker. It won't use notes or colours, just the dates - whether they are filled in or not, and just count the ones which are filled in.
It will automatically update as we update the tracker without needing to update the VO Summary. The only thing I should need to do is add a new row if we get an applicant from a new visa office which we don't already have on the spreadsheet.
I *think* it will automatically update if we move the columns on the main sheet but please be careful admins just in case
Wayne.