Problem with Individual Charts in College Pool Corrected

Oops!
Zach let me know that he found a problem with the Individual Charts tab in the 2011-2012_Bowl_Pool_Summary file. Participants 21-40 were't working properly - showing "no picks made" even if valid picks were input into the Picks tab.

I've fixed this. If you download it now, it'll work properly.

Sorry about that!

Eric

Expanded (Again) Bowl Pool Spreadsheet

It's done. At least I think it all still works. Now you have room for up to 100 participants. I also added in 5 extra bowl game rows for future expansion if necessary. For those of you with large pools, this outa work just fine. Again, you can thank JMorein and EGeierman for footing the bill.

Also, again, this new file is only compatible with Excel 2007 or 2010, as it's far wider (in columns) than is supported in Excel 2003 and older.

click here to download the file.

Enjoy!

Excel_Geek

Another expansion of the College Bowl Pool Spreadsheet is afoot!

I caved (again). I'll be expanding the current College Bowl Pool Spreadsheet to accommodate up to 100 participants. There could be some other minor changes as well. Thank JMorein and EGeierman for spearheading the effort (and footing the $50 bill I'm enforcing to do this). If any others would like to contribute to the $50, simply email me (you should be able to find my email address on this website somewhere) or send me your email using the little chat thingy indicating that you'd like to contribute, and I'll send you out the new files once they're done.

UPDATE: This expanded version will definitely only be for those on Excel 2007 & 2010, given the width in columns it'll take.

2011-12 College Football Bowl Pool Spreadsheet

It seems like the first time in a few years (not sure if this is true, but it feels like it) that I don't have to modify the spreadsheets to add another game. "Just" 35 bowls again this year.

The Humanitarian Bowl has been replaced by the Famous Idaho Potato Bowl. The Texas Bowl is now called the Meineke Car Care of Texas Bowl. The old Meineke Car Care Bowl has been replaced by the Belk Bowl. Yeah...Belk. I had to look it up too. "Charlotte, N.C.-based Belk, Inc. (www.belk.com) is the nation’s largest privately owned mainline department store company with 305 Belk stores located in 16 Southern states. The company was founded in 1888 by William Henry Belk in Monroe, N.C., and is in the third generation of Belk family leadership. The belk.com Web site offers a wide assortment of fashion apparel, shoes and accessories for the entire family along with top name cosmetics, a wedding registry and a large selection of quality merchandise for the home." Okay. Anyway...the reason I even bring any of this up is that while I know that the sponsors of these bowl games spend a lot of money to get their brands out there, where convenient, I'm just going to leave the sponsor name off in the spreadsheet -- e.g. i'll just put "Music City Bowl" instead of "Franklin American Mortgage Music City Bowl". In cases where the sponsor name IS the bowl name (e.g. "Champs Sports Bowl"), I'll obviously just use that. The new "Famous Idaho Potato Bowl" i'll just call the "Potato Bowl" in the whole "Orange Bowl", "Cotton Bowl", etc., tradition.

Now that that's out of the way, here are the files:

Click here for the "Master" file used by the pool organizer. This is the one with the fancy charts and group scoring summary, etc.

Click here for the "Individual Picks" file used by each person participating in the pool. This is the one the pool organizer typically would email out to his or her participants to have them fill out to make it easier for him or her to copy paste their picks into the "Master" file.

Enjoy, everyone!

Excel_Geek

I've already gotten a slew of inquiries as to when this year's college football bowl pool spreadsheet will be ready. Patience, Grasshopper(s). Once all of the bowls have selected their teams, it will be ready.

Not familiar with the spreadsheets? Click the "College Bowl Game Pool" tag to see what I've done in the past.

Later,

Excel_Geek

Custom Filled Pie Chart

I recently completed a project where the client asked me to design a pie chart which can be filled with colors according to selections made by the user. Each pie slice represents a task which the user had to complete. Once the user marks a task as "Completed" the pie slice should be filled with color, otherwise the slice should be white. Each task has a different importance and the client wanted the pie slices to be sized accordingly.

In my example there are 10 tasks. The data is arranged in columns as follows:

• Column A - The list of tasks.
• Column B - The status set by the user for each task.
• Column F - Relative importance of each task (in percentage). Determines the size of the slices.

I added a pie chart with column A as the axis labels and column F as the values series. I formatted the data series in the chart to have no fill. Then I added the following code in the sheet module where the chart resides:

Private Sub Worksheet_Change(ByVal Target As Range)

PieColor = Array(10973765, 4409002, 5154185, 9394289, 11507777, 4031707, 13609363, 9606097, _
    9883065, 12426153)

If Not Intersect(Target, Range("B1:B10")) Is Nothing Then

    For i = 1 To 10

        If Cells(i, 2).Value = "Completed" Then
            ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Points(i).Interior.Color = PieColor(i - 1)
        Else
            ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Points(i).Interior.Color = -2
        End If
    
    Next i

End If

End Sub

This is how the chart looks like when tasks 1, 3, 6 and 9 are marked as completed:

(Click on the image above to view its original size)

Because the code resides in the sheet module under the Worksheet_change event, every task which the user changes to "completed" is being updated dynamically in the chart. If the user clears the "completed" status, the slice of that certain task would return to be white.

Insiders will shortly receive from Eric the file which contains this chart.

Usage of 3D Chart To Ease Data Analysis

Ever wondered how a 3D chart can come in handy? I want to give an example where a 3D chart can turn a tedious task into a simple procedure. It can be very useful for a quick analysis of a data set with a glimpse of an eye.

I used to work in a call center where the shift managers had to schedule shifts for their team agents every couple of weeks. The main guideline was to conduct a periodical rotation of shifts between the agents. The shift mangers used to look on the summarized count of shifts per agent and get a big headache. For roughly 20 agents, most humans I know would get a headache... Well, I came up with a remedy in the form of aligning all that data in a 3D chart. There were only 3 types of shifts and I set the axis the following way:

• X - Agent names
• Y - Count of shifts
• Z - Shift types
  

If you click to on the above image to enlarge it, you will see two inputs at the top for the selection of the Start date and the End date. These are actually drop-down menus which determine the range of dates that would be shown in the chart. The data which is feeding the chart is based on SUMPRODUCT functions which sum up the shifts per each agent for any given date range. This is also known as SUMPRODUCT with multiple conditions.

By inspecting the chart, the shift managers could immediately conclude for instance, which agents had a majority of night shifts and prioritize these agents to do other shifts in the upcoming schedule.

Eric will dispatch the file which features the 3D bar chart and the functionality behind it to the Insiders Subscribers.

Verifying Sheet Changes

Hello Everyone,

I'm Gadi, Eric presented me in the blog last month. Starting from today I'll periodically write posts in ExcelGeek.com, it's something I promised to help Eric with, as part of our collaboration on projects. It is my pleasure to share my knowledge with others. I'll try to post neat Excel tricks, which some of you may find useful (I hope).

My first post I would like to dedicate to a solution which I lately applied at my work place, related to verifying with the user, whether the changes he made to certain critical fields in the worksheet were made deliberately or by accident. An alternative solution could have been to protect the workbook, however I did want to allow users the ability to change these values. The solution is based on a message box, that pops up whenever a sensitive cell is being changed. It prompts the user to confirm the change of the cell.

The following code has to be placed in a sheet module. Copy paste it into the modules of the sheets where you want to enable user's confirmation on any changes made to sensitive data cells. Change the VerifyRng to the range where you want the confirmation message to popup. In the following code it is set to any cell in column A.

Public Trigger As Boolean

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Resets the trigger if a new cell is selected.

    Trigger = False

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'Verifies that the user deliberately changed data in column A.

    Dim LastRow As Integer
    Dim VerifyRng As Range
    Dim Response

    If Trigger = False Then

        Set VerifyRng = Columns(1)
        'Change this variable to your desired range
        
        If Not Intersect(Target, VerifyRng) Is Nothing Then
            Response = MsgBox("You are about to change the cell " & Target.Address(False, False) & _
                "." & Chr(13) & Chr(13) & "Are you sure you want to modify this cell?", _
                vbYesNo, "Cell Change Alert")
    
            If Response = vbNo Then
                Trigger = True
                Application.Undo
            End If
    
        End If
    
    End If

End Sub

2011 March Madness Bracket is Here!

Looking for 2014 brackets? Check here.

Ok, people. Here they are -- this year's March Madness Bracket spreadsheets. Again, there's a Master sheet for the organizers of pools, as well as a Participant version, just for people to make their picks and send them to the organizer.

Master File

Participant File

Once you've downloaded the Master file and open it (make sure you've got Macros enabled), it'll prompt you to send me a "Lock Code". When you do, I'll send you back a "Key Code" to unlock the file for use. Before I send you this code, however, you'll need to pay the $2.00 price tag.

Incidentally, it took me a bit longer that I'd planned to get these out because I was caught a bit off-guard by the NCAA's use of the four play-in games. I'd ASSUMED (yes, I know what ASSUMING can do.) that each of the four play-in winners would be the 16 seed for each of the respective quarter brackets -- EAST, WEST, SOUTHWEST, and SOUTHEAST. This was not the case, though. Two of the play-in game winners will effectively be 16 seeds (EAST & SOUTHEAST), one an 11 seed (SOUTHWEST), and one a 12 seed (EAST). If you didn't catch that, yes the EAST quarter bracket has two play-ins, meaning that the WEST has none. Very odd, I thought.

As always, let me know if you've got questions.

Insiders, if you need a Key Code, let me know. It's part of your subscription.

Happy March Madness, everyone!

Later,

Excel_Geek

Wait until this evening to download this year's March Madness spreadsheet

In an effort to try to stem the many emails, instant messages, etc., that I'll get today as people are looking for this year's version of the ExcelGeek.com March Madness Spreadsheet, let me just say this:

After all of the tournament selections have been made, and the bracket has been laid out by the Selection Committee, I'll post the two spreadsheets -- one for individual participants and the other for pool organizers. That will be late this evening.

For those of you who've decided to pay the $2.00 in advance, you'll still have to wait for the files to be posted. I plan to email you, if you've paid in advance, when the files are available. Then, when you open the "master" spreadsheet (supposing you've got your macros properly enabled), you be given a "lock code". Send this to me via email (address you may already know, but if not, it's provided in the file), and I'll send you back the associated "key code" to give you access to the content of the file.

Now...back to updating those files...

Heat Charts are so easy in Excel 2007

Before you roll your eyes and say, "Welcome to life after 2003, Excel_Geek," let me just say, "I know, I know." The driver for this project was that I was analyzing my website traffic using my Google Analytics account. I noticed a few things.

One was that "heat chart" or "heat charts" seems to be a pretty popular search term for folks to find me. I've obviously done a couple of heat chart projects, but back when I was still using 2003, so in order to create them with more than four colors (the limit to Conditional Formatting in 2003), I had to do write some VBA code to accomplish it.

Another thing I noticed what the rhythmic up and down of my site traffic. There were a few major spikes -- March Madness season, college football bowl game season, and one recently when @MSExcel mentioned me in their Twitter feed. What I wanted to further analyze, though, was if there was any pattern to traffic patterns based on Day of Week and recency of blog posts.

I combined these two findings and decided to do a heat chart incorporating both Day of Week and recency to blog posts. Here's what the chart itself looks like:



Along the way I used some other favorite techniques along the way: indexing the data (so I'm not sharing my actual web stats...hey, that's my business), a trick to convert a list of dates into a list of days of the week, VLOOKUP using TRUE instead of FALSE (whatever for? right?), and using SUMPRODUCT to create COUNTIFS and SUMIFS functions that will be backward compatible to Excel 2003 and earlier. I highlighted the creation of the heat chart and these other techniques in the source file, which I'll be sharing with my Insiders Subscribers.

Later,

Excel_Geek

2011 March Madness Bracket is coming soon

Mark your calendars, folks. March 13th is Selection Sunday. In what has now become a five year tradition, I'll be releasing the ExcelGeek.com March Madness Bracket spreadsheet late that evening, once the picks are known.

In years past I've charged $3 for the "Master" version of the sheet -- the one for the organizer of your office pools, etc. I used the opportunity to showcase (read: show off) a system I've developed for locking down and protecting, then individually licensing access to my spreadsheets. I plan to keep it locked down (damn, right?), but I'm dropping the price for the Master Spreadsheet to $2. Once you pay via PayPal and send me the "Lock Code" given to you by the spreadsheet once you open it (and have your Macros enabled properly), I'll send you the "Key Code" to gain access.

Can't wait...

Excel_Geek

Updated: Parse Google Search Results for First URL

Those of you who've been following me for some time may know that I've done quite a few projects related to parsing Google search results for various data -- URLs, number of results, etc. Those of you using the code likely know that some time ago Google apparently made some changes to how they put together their site causing my stuff to stop working. Well, recently one of the requesters of one of these projects - one that returns the first non-paid URL returned - came back and had me update it to work with how Google's site currently displays search results.



This time I made it into a user-defined function. You simply enter the function...



=getFirstGoogleResultURL(A2), where the value of cell A2 is the search term.


It works pretty slick, but know that Google will not allow this sort of thing to be used on large volumes of searches. I'm pretty sure they'd much rather have you using their APIs for this sort of usage.

Excel_Geek Insiders subscribers, your copy of this file is on its way.

Later,

Excel_Geek

Introducing Gadi, My First ExcelGeek.com Contributor

Everyone,



I'd like to introduce you to Gadi Bizinyan.




Gadi is originally from Isreal, but is currently working in Bangkok, Thailand. Gadi's got a Bachelor's degree in Economics and Business Administration and really got into Excel as part of that curriculum. His self-described specialties in Excel are creating dynamic databases, manipulating data, analyzing statistically and graphically, and financial analysis.



Something you may be surprised to learn is that Gadi's been helping me for some time now. Some $50 Projects and some Private Projects have been his handywork. Remember, I've got a full-time "real job" as Director of Sales for Stanley Healthcare Solutions, so I cannot dedicate nearly as much time to this overgrown hobby as I'd like. Gadi's helping me to continue to help people in need and do some neat projects. Gadi's also going to begin contributing posts to the blog, too. Please welcome him and pretend you like his stuff as much as mine. ;-)



Welcome aboard, Gadi!