I am attempting to create a posting report that will generate automatically based off a separate worksheet that holds a calender that allows the user to use data validation lists to select the posts that an individual will hold. I will try to explain the situation as best that I can:
The first sheet contains the roster template that I would like to use for a printable report. At the top of the first sheet there are two cells that will allow the user to input the calender dates of the specific roster they want to generate.
The second and third sheet are examples of the calendars that I would like to use to input the posts that each member will be assigned to. The days applicable use data validation lists to assign the posts.
Here is what I want to do but seem to be stuck at and need help:
The user inputs the assigned posts in the calendar, then they move to the first sheet, the roster, and inputs the date they need to generate the roster for. I need the cells (that each read #N/A currently, to look at that date and month, pull up the appropriate sheet based on month, look at the date used, and then will put in the names of each post as they are assigned on the calendar.
The formula currently in the cells correctly reads the information put in the date, and through Lookup function looks in the appropriate location and tries to pull the name for that row. What I am currently having problems is that sometimes the formula does not pull the correct information, returns an error, or for some reason returns a completely different name from the list. I am trying to put this together using only excel functions rather than VBA coding, as the computer security automatically disables macros and would make it annoying for the user to enable them every time this workbook is opened. There are other functions that I want to do with the product, but I'm aware of how to implement them, and just want to get the base project complete before adding them. I have attached the workbook in question. Any help would be very appreciated.
Bookmarks