Extract data from dynamic range of non-contiguous cells

[bdumaguina]

Hello members,

I know it's lengthy, but I do hope you find the time to give me some advice, thank you in advance.

2 worksheets. First worksheet has a table which works as a database of clients, with dates and activities. Column A has all the clients. Column B has dates, Column C has the activity accomplished during that date (in column B. --- now column B and column C repeats itself indefinitely towards the right. I believe this is called dynamic, the table is dynamic. Therefore if a new client comes in, I just add a row at the very bottom. And if I run out of columns to the right to add dates and activities, I simply add some more.

Second worksheet is a dynamic monthly calendar. I just set the month and year and the sheet adjusts itself. It should also work to extract the company name depending on the date of day.
I'm trying to work a formula that will be placed under each day of the month that will lookup the entire database in sheet 1, and return the company name with an important activity that day.

For example, in sheet 1; I have Client A, Date 1 entry is Aug 14 2011. Activity 1 entry is "initial meeting". After that meeting a follow up meeting was decided on Aug 22 2011. So on Date 2 entry I place Aug 22 2011. Activity 2 entry "Follow up meeting with HR dept, 10am".
I don't have time to go through sheet 1, check each client, check the last entry, and see if there are any important deadlines. I just check sheet 2 (monthly calendar). Look at the day TODAY, and see which clients needs my attention.

I could not create a lookup, or index/match formula because of the dynamic sense of the data. Plus it's an array on non-contiguous ranges. And it must not stop looking up the database once it finds the date. As in the example, Aug 22 2011, so it returns Client A, what if I have another Client (Client S) who also has a deadline on Aug 22. The calendar should also show Client S.

Thank you very much. Your help is much appreciated.

 

[AlphaFrog]

It begs the question of; Why are you using Excel for this? There are a variety contact management and scheduler software packages available (Outlook's calendar for one) that would, pardon the expression, excel at this and have many more useful features to sort, group, and visualize the activities.

 

[bdumaguina]

It begs the question of; Why are you using Excel for this? There are a variety contact management and scheduler software packages available (Outlook's calendar for one) that would, pardon the expression, excel at this and have many more useful features to sort, group, and visualize the activities.

hhhmmm...Now that you mentioned it; I think I will look into Outlook, never used the program. Thank you for your suggestion. Recommend any good reference to learn from MS Outlook? Thank you.

I'm still open to suggestions regarding the matter though.