I'm a new subscriber. My apologies for the depth of all this, but I first need to provide a solid foundation on which to build up to the problem I'm trying to solve.
I've attached a sample file ( sample schedule.xlsm 60.91KB 330 downloads):
- The tab "Schedule" is my pivot table based on the table in tab "Raw Data"
- The tab "Raw Data" tracks: meetings; who is attending those meetings; what products we'll be discussing along with the divisions those products belong under.
- The tab "Attendance" is my attempt at providing an overview of the attendance (not completed)
- The tab "Menus" feeds column F on tab "Raw Data".
- The tab "Data" is what the table in "Raw Data" uses to populate several cells with VLOOKUP.
- I need the data to remain dynamic, so if I need to add someone to a meeting, move them to another meeting, change the products for a meeting, then existing data—with all its associations—will need to remain intact.
- Attendees can attend more than one meeting. This is why you'll find repeated dates in the left column on Raw Data and repeated names in a few columns over to the right. But you can also find them in future meetings.
- When I send meeting invitations to attendees, I'm asking them to respond to me so that I can keep track of their responses which will be:
- No RSVP
Here's what I'm trying to solve and need your help with please:
- I know I could use pull-down menus to show a specific value based on the person's response, in fact, I really like that idea (John accepts the meeting invitation, so I select "Accept" from the pull-down menu next to his name, for example—this does not currently exist in my sample file yet). This is what I would like to do with the pivot table on tab "Schedule":
- By date, show the company division, then the attendees for that meeting, and then the products being discussed for that particular meeting. This much I have done, but here's where it gets a little tricky…
- If a person has accepted the meeting, I want to show the cell with their name in it with a green fill and white text; "declined" will have a red fill and white text. And something similar for the other values.
- If this can be done at all, then I'm thinking it would be a combination of using several VLOOKUPs and conditional formatting based on a formula and applying all that to the pivot table.
Here's how I envision the logic:
- The VLOOKUP would search on the date in the pivot that's in "worksheet X" and if it matches the date in a table in "worksheet Y", then I know I'm in the right meeting.
- HOWEVER, the next step would be to compare all the names between the two tables for that meeting and if THEY match, then I would find their associated RSVP value (accept, decline, whatever) and use that to determine what cell style it should have.
Still with me? J
Concerns (as if I need more, right?)
- I'd like to avoid VB script if possible because I think I stand a better chance of understanding formulae rather than programming at the moment.
- A scenario to bear in mind: If John, Mary and Sue cancel their meeting, I could fill it with other attendees by just selecting their product(s), which will then automatically populate the appropriate cells (via VLOOKUP) with their names and company division.
- There's a possibility that I may have to add/edit/remove names from my roster (the source I'm using with VLOOKUP on tab "Data").
Admittedly, I may be over-complicating this or at the very least thinking Excel can do something that I see in my head when it actually cannot. Either way, I welcome any alternative and assistance. Thanks!