Highlight First Blank Cell With Conditional Formatting

Hey, just wanted to share a quick trick about conditional formatting.

If you have a data entry sheet with input in adjacent cells within the same row or column, and you want the next blank input cell to be highlighted with a color, you can apply conditional formatting based on a formula.

The following formulas assume that your input is starting from cell A1 and all the input is in adjacent cells:

• Vertical data entry:   =ROW(A1)=COUNTA(A:A)+1
• Horizontal data entry:   =COLUMN(A1)=COUNTA(1:1)+1

If there are empty cells before the actual input range, you need to modify the formula. Increase the "+1" with every empty cell above (or to the left of) the actual input range. This type of conditional formatting can be applied on the entire column, entire row, or custom range, and there would only be one highlighted cell at a time.

Excel Geek insiders will get the sample file from Eric.

2012 March Madness Bracket Files Are Here!

Here we go, everyone!

Despite being in the middle of a bit of traveling, I've managed to get you the much anticipated ExcelGeek.com March Madness pool manager spreadsheet (and associated individual picks spreadsheet). Thank goodness Hampton Inn is a comfortable place to work, right?

The system works the same as last year. There are two files: The first is the "master" spreadsheet, used by the pool organizer to track the results of everyone's picks vs. who actually wins, etc. This is where you determine how many points a correct pick in each of the rounds is worth, as well. Also, the pool manager can make his or her picks in this file, too. The second file is the simplified "individual picks" spreadsheet used by the participants in the pool to make their picks and send the file to the pool organizer to pull their picks into the master.

• "Master" spreadsheet ($2.00)
• "Individual Picks" spreadsheet (FREE)

Once again, the individual picks spreadsheet is free, but the more complex "master" spreadsheet is not. It is ONLY $2.00, though. The files are both locked down and protected. In the case of the master spreadsheet, it requires a "key code" to unlock the magic.

Unlike years past, though, I won't make you email me the "lock code" the file presents to you when you first open it and then email you back a "key code" to unlock it. Instead, I've automated the process.

What you will do is the following:

1. Download the "master" file by clicking on the link above.
2. Open the file. Make sure you've got macros enabled. If you didn't have macros enabled when you first opened the file, enable them, then close and reopen the file, making sure to enable macros. If the file doesn't display a "Lock Code" when you open it, something didn't work right. Try again.
3. Copy the "lock code" the file presents you.
4. Paste that code (be sure to delete any trailing spaces added when you paste it -- it normally adds an extra trailing space for some reason) into the "Lock Code" field below.
5. Click the "Buy Now" button below to pay the $2.00 for the file via PayPal. You do not need to have a PayPal account to pay this way. Any major credit card will work just fine.
6. At the end of the transaction, you'll be redirected to a confirmation page with my pretty little face on it. It will display (among other things) the "key code" you'll need to activate the file.
7. Copy and paste that code into the spreadsheet into the proper place, click submit, accept my terms and conditions, and you're off and running!

Again, if you don't paste in your Lock Code and complete your transaction, you won't receive a "Key Code" that will actually work to unlock your file. Do not forget this part! You may have to pay another $2.00 if you mess it up.

*Lock Code (absolutely required -- read above)

Best of luck, everyone!

Excel_Geek

2012 March Madness Bracket is coming...

We're under two days until Selection Sunday! You know what that means, right? That's right. It's almost time for another version of the ExcelGeek.com March Madness Bracket pool manager spreadsheet (and associated participant version).

I've got something new to show you all this year. OOOOooooooo..., right? Well, actually the pool master spreadsheet and associated participant picks sheet both will look and feel and work pretty much like they did last year. The big difference this is that you won't have to email me the "Lock Code" you get when you first open the (locked down and protected) "master" file. Instead, I'll give you a place to enter that code so that it is submitted when you pay the $2.00 via PayPal. Then, when the payment is processed, you'll be taken to a page that automatically generates the associated "Key Code" you'll need to put in the spreadsheet to make it work.

How about that? No more waiting on me. Instant gratification! (And, I don't have to struggle to keep up with emailing people codes.) You didn't know I was also a budding web developer did you? In all seriousness, I have a good friend, Craig, to thank for being patient enough to "teach" me a bit of .php. Thanks, Craiggers!

As soon as all of the picks are announced on Sunday, I'll get the files posted.

Talk to you all again in 48 hours or so.

Excel_Geek

Data Validation Quirk When Using Named Ranges

So here's a goofy item I stumbled upon today. (Others of you, I realize, have known about this for some time.)

Here's the situation:

1. You are using Data Validation to restrict input for a particular cell to items from a list (Data --} Data Validation --} List).
2. You have the "Ignore Blanks" option checked.
3. The list you are referencing is a Named Range.
4. The list you are referencing contains one or more blank cells.

What happens, you ask? Well, it lets users input any value into the cell that's supposed to be data validated without warning. Nice, right?

Three simple options to fix:

1. Uncheck the "Ignore Blanks" option.
2. Don't have any blanks in your Named Range.
3. Don't use a Named Range.

Just thought I'd pass it along...

Later,

Excel_Geek

Bowl Season Wrap-Up

Well, another college bowl season is in the books. For those of you keeping score, Mrs. Excel_Geek beat Excel_Geek for the third straight year (or so). And in a nail-biter, new-comer Excel_Geek Jr. matched his daddy for the number of correct picks, but was narrowly edged out by 8 confidence points. So you know, I picked for the little guy, using the =RANDBETWEEN() function and the =RAND() function -- maybe I'll do a post describing how to do that -- to make his picks completely randomly. That's right. My own, well-thought-out, analyzed, expert picks did only very slightly better than completely random picks and point assignments. Oh well...back to the drawing board. See you all next bowl season!

Later,

Excel_Geek

Oh, almost forgot some traffic stats: the 2011-12 Bowl Season (12/3/11 - 1/9/12) increased ExcelGeek.com website traffic by nearly 3.9x the average daily site traffic for the year leading up to it, whereas the 2010-11 Bowl Season (12/4/10 - 1/10/11) increased traffic by (only) nearly 3.4x. 2011-12's peak 20-day moving average traffic day (12/23/11) was nearly 44% higher than that from 2010-11 (12/24/10). The single day peak from 2011-21 (12/5/11) was 72.8% higher than the single day peak in 2010-11 (12/6/10). Overall...a great year.

Next up...March Madness!

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