Sensitive table

About ten years ago I wrote an Excel spreadsheet that today needed some maintenance. The request was simple, 'add space for ten more teachers.' The hard part was to remember how on earth I had created the spreadsheet.

One hour of research revealed that I had used sensitive tables, something I have not used since.

The first column contains the teachers' ids and the second column how many lessons they have been assigned during a week. There are eight periods per day and the other columns show if they are teaching (1) or not (blank).

The cell with # in it contains this formula:
=COUNTIF(TIMETABLE,DUMMY)
where TIMETABLE is a range on another sheet that contains amongst other things all the teacher ids for every period of the day during a week while DUMMY is the name of a cell in the first column below all the teacher ids.

The cell with 1 in it for Monday contains:
=COUNTIF(MON1,DUMMY)
where MON1 is a column on another sheet listing the teachers who teach first period on Monday.

Now we are ready for the exciting part! The range from the blank cell above AE down to the row with the last teacher and the column with period 8 on Friday I created to be a sensitive table by choosing Data - Table on the menu and A93 (the address of DUMMY) for Column Input Cell (Row Input Cell I left empty).

When I click on any of the cell the formula displayed is
{=TABLE(,A93)}.

I found this solution rather elegant, although it was hard to gather how I had done everything ten years ago. Excel's help on this is meager and I hadn't taken any notes. Which is why I jot this down in case I want to use sensitive tables again. Here is a web page that helped me a bit.