Automatically insert based on addition to an alphabetically sorted list above?

[k1s]

Hi all, I'd greatly appreciate some thoughts on ways to approach solving the following problem.

I've got a set of spreadsheets that record pupil assessments, and I've run across a problem when I add a new pupil half way through the school year, related to having the names sorted alphabetically.

The data entry sheets (where the assessment scores are entered) have the pupils names along the top row. The names are transposed there using an array formula referencing a named range which is a column in 'Table 2'. 'Table 2' is generated by a power query from Table 1. - See 'BEFORE' in the picture below and simplified example attached:

The problem is, if I add a new pupil to Table 1 (and refresh the query/Table 2), the scores and names no longer align, because while the new pupil name is automatically been inserted in to the names row in the right place, the columns underneath aren't automatically shifted over.

Can anybody think of a way of inserting a column of blank cells under the newly added name if a new pupil is added?

(I know I could do it manually, but I have 144 sets of scores like this that might need updating!)

Many thanks

 

[bosco_yip]

1] "Input Table-Table 1" should be tired with manual entered pupils' name and scores

2] In "Output Table" M25, formula copy across and down :

=IFERROR(1/(1/INDEX($B$25:$H$100,MATCH(M$24,$A$25:$A$100,0),MATCH($L25,$B$24:$H$24,0))),"")

3] See attached file

Regards
Bosco

 

[k1s]

Hi,

Thanks very much for taking the time to reply.

Table 1 is just where the master data for each pupil is held (name, demographics, etc.). It can't be the data entry place for the assessment scores because there are multiple tests, at multiple times of year, etc., so the simplified example I posted, would be one of more than 150 data entry places.

I need Table 2 I need to create a sorted list of pupils for just one class (the actual Table 1 has details for all classes, and much more).

As far as I know I can't make the header row of an excel table contain a formula and hence that is why I have been using the transpose function referencing the name column in Table 2 and having the 'flat' table beneath that row.