Solved

Posted on 2011-10-25

Hello,

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)*

For example:

• 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)*

Thanks

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).

For example:

• 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?

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

Thanks

17 Comments

=INDEX($G$2:$M$6,$B3,MATCH

=INDEX($G$2:$M$6,$B3,MATCH

Please see attached. There are two sheets - "String" assumes that the date is simply a string as shown, whereas "Date" assumes it's a formatted date, take your pick!

I've cheated and

My formulas are highlighted in yellow -

"String"

=IF(B3="","",OFFSET($F$1,A

"Date"

=IF(B3="","",OFFSET($F$1,A

Regards,

Brian.

Assignments.xls

Formatting for the date column is: ddd, mmm dd. The dates were created simply by entering "10/1/2011" in cell C3, then "=C3+1" in cell C4 and pasting it down. Everything else was entered manually.

Try one of these formulas

Try them by pasting in which cell?

Neither formula appears to work:

1st formula puts Paul in cell D10. 2nd Saturday is Dave.

2nd formula results in #N/A

Please see attached.

That's it Brian! I had the first part using =OFFSET() but I needed help with setting up the =MATCH().

Thanks a bunch! :)

=IF(B3="","",OFFSET($F$1,A

Title | # Comments | Views | Activity |
---|---|---|---|

Sorting the contents of a text string in Excel in Alphabetical order. | 8 | 44 | |

Excel add date to text | 3 | 33 | |

Sum Per Month | 7 | 23 | |

"Microsoft Excel has stopped working" error when getting external data from Access | 10 | 25 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**19** Experts available now in Live!