Search code examples
vbaexcelworksheet-function

Determining whether VBA is necessary in Excel, or if a pivot chart/table can suffice

I wish that I had a more precisely descriptive title for this question, but I am not very knowledgeable with Excel spreadsheets or VBA (I'm primarily a Java developer, and don't play much in the Microsoft world).

I'm trying to use an Excel spreadsheet to accomplish something. I need to determine whether I can get there with a PivotTable, PivotChart, or some other built-in functionality... or whether I need to go down the path of writing custom VBA code (or maybe using another platform altogether).

The purpose of the spreadsheet is to help create schedules for an organization (a Toastmasters club). This organization has a roster of members, it meets weekly, and various members are assigned to various roles in a given meeting.

My spreadsheet looks like this:

First Tab

One column... a list of names representing the membership roster.

Second Tab

Each row represents a past meeting date. There are columns for each role, and the cells are to be populated with who served that role on that date. I use Data Validation to have the first tab's roster available inside each cell as a pulldown.

Third Tab (maybe multiple tabs?)

Here's the point of the whole thing. For each of the possible meeting roles, I would like to see which members are the most "overdue" to be assigned for that role. Basically, I want a list of all the club members, sorted in order of how long it's been since they last served that role. People who have never served in that role would be sorted at the top of the list.

Is that third tab data something that can be accomplished with a PivotTable, etc... or am I misunderstanding the purposes and limitations of those tools?

Solution

  • My first instinct is to move you to Access where you can SQL query to your heart's content, but I'm sure this can be done in Access without any custom coding.

    1. PivotTables may work, but I avoid them for several reasons (hard to describe to casual users, doesn't update automatically, hard to format, etc.), so here is something using just Excel's built-in cell functions.
    2. Check out vlookup() and countif() on http://www.techonthenet.com/excel/formulas/index.php. (The built-in help files work too. I just like this site more)
    3. In the image below, I have simulated your Tab2 and Tab3. Tab2 is purely data with no equations. Vlookup() requires that this table be sorted by Date Descending and with the date column on the far right.
    4. Tab3 counts both the # times each person has served in that role and when they last worked that position.
    5. The formula in cell C13 is =COUNTIF($B3:$B8,$B13). i.e. it counts the number of times that "Abe" appears in the Chairperson column of Tab2.
    6. The formula in cell D13 is =IF(C13>0,VLOOKUP($B13,$B$2:$D$8,3,FALSE),"-"). i.e. if that person ever served in that role, then it finds the most recent date.
    7. The formulas for security are almost the same. The "Last Served" column is now =IF(E13>0,VLOOKUP($B13,$C$2:$D$8,2,FALSE),"-"). The ranges need to be renamed here to satisfy the needs of the Vlookup function (a SQL query would be really convenient here, but Excel doesn't allow that)
    8. It's hard to define if someone is 'overdue' because some people will argue that "Last Served" is more important than the "# Times Served" and others will argue the exact opposite. I suggest looking at both columns and just talking it over. Sample Image