1
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?
Doesn't the LOOKUP formula require that the 2nd parameter's cell range be sorted? – Steve Perkins – 2011-01-26T13:08:44.997
Yes, in ascending order, forgot to mention that. You can separate roles in the same tab and sort members alphabetically for each, hence the date row would also be in ascending order. – Gani Simsek – 2011-01-26T13:16:00.477
Thanks for this idea, I had not expected to find a way to do this without pivot tables or VBA (which will make things easier for the next person who inherits this spreadsheet). However, I don't trust LOOKUP's behavior of using the "next largest" match when there is no exact match. So I think I'll move the Date column to the far right, and use the VLOOKUP function instead. – Steve Perkins – 2011-01-26T14:55:26.653