Hello all, hope you can help me out with this one.
I have a sheet 'Inspections' containing a list of around 150 inspections and other events, each with an associated 'owner' and due date. The name of the inspection is in column E, a single letter 'type' for each inspection is in column F.
I have another sheet 'Tracker' that depicts Month/Week, by owner. I have 20 owners, and of course each year has 52 weeks, so sheet 2 covers a range of 20 rows and 52 columns (so far--I will likely expand the columns eventually to project out past the current year).
I employed a VLOOKUP formula in sheet 'Tracker' to look at sheet 'Inspections' and determine what kind of entry, if any, goes into each block--results are the single letters (I, C, A, V, G, S, or T) from column F in 'Inspections.' The actual inspection name isn't displayed, only the single letters, so you can see the whole year without having to scroll forever. Bottom line is that if an owner has an inspection in a given month/week (entered in 'Inspections'), the display in 'Tracker' is a single letter depicting what kind of inspection is due that week.
Example--In 'Inspections,' onwer Mary (column B) has an inspection named Home Property Management Audit (coumn E), represented by a 'C' (column F) due 4/9/2013 (column C, but that's not important). Sheet 'Tracker' shows a 'C' in row Mary, column 4/2 (the second week of April).
Here's what I need--What kind of Macro code can I use to automatically attach the actual inspection NAME ('Inspections' column E) into a comment, so that when I see Mary's 'C' I can mouse over it and have the comment box show 'Home Property Management Audit?' Oh yeah, preferably without my name attached.
It would also be nice if all the blank blocks would have no comment attached to it at all, so that only a comment tag shows up if the VLOOKUP formula results in one of those letters, but that seems even harder--for now, I would just like to avoid having to manually populate all these inspection names to each individual block!
Anyone?