I am working on a Excel workbook which has a number of identically laid out worksheets. The worksheets contain info for different schools and each worksheet is named according to the school the info is related to. I want to organise the information of these worksheets onto an overview page. Currently I am using a ‘VLOOKUP’ function (e.g. VLOOKUP($A4,’Betty Layward Primary’!$B$13:$G$107,6,FALSE) to do this (where Betty Layward Primary is one of the schools in question – each time I move onto a new line I have to physically type out the name of the new school in this part of the function) but I wondered if there was a more ‘automated’ way.
Is there a way in which I can rig the function/formula in such a way that the formula automatically works its way from worksheet to worksheet automatically inserting the ‘right name’ in this part of the function and returning values in the overview page.
I would be grateful for any pointers
Many thanks