Let's say you are using an Excel spreadsheet for the purpose of making assignments in a group of people for the next 12 months to do some task once each month.
In the spreadsheet, suppose column C contains sequential dates of the month and column B contains a number indicating how many times that particular day has occurred that month (Fig. 1).
(Note that rows 13 thru 33 have been hidden in Fig. 1)
• Cell B5 = 1, indicating that October 3rd is the 1st Monday of the month,
• Cell B10 = 2, indicating that October 8th is the 2nd Saturday of the month,
• Cell B12 = 2, indicating that October 10th is the 2nd Monday of the month,
• and so on.
Also, suppose that the same spreadsheet contains a table of names (Fig. 2), in which columns G:M are headed by the seven days of the week and column F contains 1st thru 5th (corresponding to the numbering in column B) to designate to which occurrence of a particular day each person is assigned.
What formula would accurately populate column D (Figs. 3 & 4) with the correct names from the table?
(Note: All Figs. show the same spreadsheet. Figs. 2 & 3 show the assignment columns and the table separately [so they are easier to see] whereas Fig. 4 shows both of them together.)
(Note that rows 13 thru 33 have been hidden in Fig. 3)
For example, the table indicates that:
• Gary has the 1st Friday of each month so his name should be displayed for Oct 7, Nov 4, etc.
• Paul has the 1st Saturday of each month so his name should be displayed for Oct 1, Nov 5, etc.
• Dave has the 2nd Saturday of each month so his name should be displayed for Oct 8, Nov 12, etc.
• and so on
(Note that rows 13 thru 33 have been hidden in Fig. 4)