So this is going to probably be a bit confusing, but I will try to explain the best I can.
I have a workbook that is intended to keep track of tasks, due dates, and assignees over seven projects. The workbook is made of three primary sections:
- Seven Project spreadsheets (Static)
- One Master spreadsheet (Static)
- One Calendar (Dynamic - the calendar displays the current week and several following weeks, with the calendar adjusting weekly as to eliminate space taken up by past dates)
The project tracker lists are working well and do everything I need to catalog what tasks we have, when they need to be done to meet the project deadline, and who needs to work on them.
I was also able to put together a "Master" or combined list; again, working well as it consolidates all of the tasks across all the project spreadsheets.
However, I've gotten to a point where I have a calendar and would like to fill it with the task entries from the Master list based on the due dates so that tasks can be viewed at a glance to see what is due, when they are due, and who needs to do them. I am struggling to find a way to do this as it just displays the first matching result on that calendar day repeatedly instead of producing any unique results it finds. Here is a sample of the data I am working with (this is mock data, not the actual data I am using, as each project contains more than 60 rows of tasks):
These are each in separate sheets all in one workbook. I just want to make that clear. Each one has a set number of tasks (static number of columns and rows), but the dates and the people assigned can vary.
This is the "Master" sheet that is a compilation of the (7) project sheets and has an added column to pull the data as I would like it to be displayed in the calendar into a single cell for each task. (Again this is static as the number of columns and rows are constant)
Sample of Calendar Desired vs. Result
Finally, the calendar. On the left is what I am trying to get as an end result, and on the right is what keeps happening. I have five cells below each date that all look to the date they pertain to then, using a VLOOKUP, it scours the Master spreadsheet for matching values.
The formula I am using:
=IFERROR(VLOOKUP([Date],MASTERSHEET![MasterData],[DueDateColumn#],FALSE),"")
It grabs the data from the date I specify perfectly!... but just finds the first value it matches in the Master sheet and uses it for each one. I tried converting each calendar date to be their own five-row mini-table, but I'm not actually that familiar with how tables work or their limitations/possibilities. I am familiar with VBA and am not afraid of going that route if I have to, but would like to know if anyone has any ideas on how to make sure that the cells under the calendar date remain unique to one another and only displays as many results as can be found in the master list for that date (doesn't restart the list once it has ran out of unique tasks)?
Any guidance or ideas are welcome. I apologize if this forum is not meant for these things, but I haven't been able to successfully create an account with "Excel Forum" and the admins do not seem to be very responsive to inquiries about account issues.
3 Reset to default
