Drop down menu, with editable options

OK, for kisses, I think I can work on this some more.

However, so that you may be in a position to maintain this yourself in the future, I'll try and explain what is going on ... and what needs to change.

In simple terms, you should be able to add variables on Sheet1 with no problem. Similarly, you can add Staff on Sheet2 and their corresponding tests. That's because the named ranges are dynamic, at least in terms of the number of rows. However, the table on Sheet2 is a fixed width and I suspect that's the problem.

So, to explain a little more. The Staff dynamic named range is defined as: =Sheet2!$B$4:INDEX(Sheet2!$B:$B,COUNTA(Sheet2!$B:$B)+COUNTBLANK(Sheet2!$B$1:$B$11))

The way that works is that the first cell in the range is Sheet2!$B$4 ... straightforward. But the second cell comes from indexing column B and the way it does that is to count the non-blank cells in the column ( COUNTA(Sheet2!$B:$B) ) and add in the number of spaces in cells B1 to B11 ... that's just to give a little flexibility if you move the table up or down, that is, add or remove rows. That's how the staff table works, however, it is a single column ... column B.

TheTable dynamic range is defined in exactly the same way but is multiple columns. But the structure of the formula is the same, and for 20 tests, it looks like this:

=Sheet2!$B$4:INDEX(Sheet2!$V:$V,COUNTA(Sheet2!$B:$B)+COUNTBLANK(Sheet2!$B$1:$B$11))

To change it to cope with 25 tests, change the two references to column V to column AA, thus:

=Sheet2!$B$4:INDEX(Sheet2!$AA:$AA,COUNTA(Sheet2!$B:$B)+COUNTBLANK(Sheet2!$B$1:$B$11))

Following this, if you wanted to extend this to 50 tests, change the V (AA) to AZ, thus:

=Sheet2!$B$4:INDEX(Sheet2!$AZ:$AZ,COUNTA(Sheet2!$B:$B)+COUNTBLANK(Sheet2!$B$1:$B$11))

It wouldn't hurt to change it to BZ now to allow for further expansion (76 tests). So long as you don't refer to them on Sheet1, no-one will know.

=Sheet2!$B$4:INDEX(Sheet2!$BZ:$BZ,COUNTA(Sheet2!$B:$B)+COUNTBLANK(Sheet2!$B$1:$B$11))


The attached updated example has 300 staff and 76 tests (although there is no data in the last 26).


I hope this all makes sense and you will be able to adjust it to meet your requirements.

Regards, TMS