Jump to content


Photo

Can conditional formats with VLOOKUPs and tables be achieved?

VLOOKUP data model relationships pivot tables

  • Please log in to reply
3 replies to this topic

#1 phoenixrizing

phoenixrizing

    Advanced Member

  • Members
  • PipPipPip
  • 122 posts

Posted 30 July 2014 - 09:42 PM

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 (Attached File  sample schedule.xlsm   60.91KB   324 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.

 

Constraints

  • 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:
    • Accept
    • Decline
    • Tentative
    • 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!



#2 phoenixrizing

phoenixrizing

    Advanced Member

  • Members
  • PipPipPip
  • 122 posts

Posted 31 July 2014 - 07:31 PM

I feel like I've scared everyone away. :)

 

Granted, I think what I'm asking for is complex so maybe more time is key.



#3 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 843 posts

Posted 31 July 2014 - 11:30 PM

Hi phoenixrizing,

 

Sorry for the delay, it's taken me a while to figure out exactly what you're trying to do. You might not realize it, but you're really trying to build a relational database.

 

For example, on the Raw Data sheet, you have columns named Participant 1, Participant 2, Participant 3 and Participant 4, meaning the meetings can only have a maximum of 4 particpants.  In a relational database, you would have two tables named Meeting and Participant that were joined together using "primary and foreign keys".  This would allow you to create meetings with as many participants as were needed.

 

Looking at your data, you would probably create separate tables named Division, Product, Meeting, Participant, MeetingParticipant and MeetingProduct in order to create the structure you're looking for.

 

Although Microsoft Access is usually considered to be the best tool for creating relational databases, Excel 2013 has introduced the ability to create relationships between tables. For the first time, this makes it possible to create relational data structures in Excel.

 

Primary and foreign keys are explained in: Expert Skills Lesson 6-1 Understand primary and foreign keys.

Creating a relational data model is covered in: Expert Skills Lesson 6-2 Create a simple data model.

You'll also find many more lessons on this topic in Expert Skills Session 6, including instructions on how to extract data from your data model using pivot tables and OLAP cubes.

 

Regarding your query about adding a VLOOKUP function within a pivot table, I'm afraid that isn't possible in Excel. However, with the structure I suggest, you should be able to achieve the result you need by adding an RSVP field to the MeetingParticipant table and then referencing it in your table.

 

I hope that this is useful information - I think a relational data structure is definitely what you're looking for here.

Please feel free to reply to this post if you need any guidance in creating your data structure and I'll be happy to help.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#4 phoenixrizing

phoenixrizing

    Advanced Member

  • Members
  • PipPipPip
  • 122 posts

Posted 01 August 2014 - 03:23 AM

Jonathan,

 

You're right: I had no idea I was trying to build a relational database. But knowing I have the mindset for them, I hope that will work in my favor when I move in that direction.

 

I added too many columns for those extra participants, but to clarify, the number of attendees can fluctuate. So in addition to the three people with job titles in the headers, there can be others who I won't know until later. Could be one, three or whatever and I was just setting up the spreadsheet to allow for a maximum and not mess things up if I made it too short.

 

As for tracking RSVP status, I was thinking an alternative would be to have a cell that stores that information for each participant (e.g., "person's name", "accept"; "person's name", "declined" -- and so on). Not as elegant as I had hoped, but should work though it will double the number of columns. But I have some ideas in mind to make managing such a worksheet not so daunting.

 

I'll take a look over the links you provided. Already, I took a look at the extract video. Very cool.

 

Thanks again!







0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users