Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm running a March Madness bracket tournament using Excel spreadsheets. I have one sheet where I will input the actual winners. I have an additional sheet per participant that is near-identical, only it contains their picks for the winners. I already have formulas set up to track scores, but I'm hoping to track visually, who picks winners and losers on each individual bracket.

How can I scan a participant's entire sheet to see if the winner they picked in cell A3 matches the actual winner shown in the winner sheet, cell A3, do this for ALL picks, and highlight matches in green and losing picks in red?

share|improve this question
1  
What have you tried? Have you looked at conditional formatting? What version of Excel? You might want to check this question out. –  CharlieRB Mar 14 at 19:08

1 Answer 1

Conditional Formatting will definitely work, but it can take awhile to set up.

Lets assume that the first sheet in you workbook is the master winners spreadsheet. Let's call this sheet "master"

Lets also assume that you have a blank XL sheet that acts as a participant template. I'm going to call this "part_tmpl" (Obviously it's easier if the part_tmpl looks EXACTLY the same as the 'master') It doesn't have to be, but for the purposes of this example lets assume its the same.

On the part_tmpl in cell B-5 your partipants will place their guess as to who will win round 1, game 1... on your master, B-5 is updated to show the results of round one, game one.

You are going to add Conditional Formatting to the part_tmpl at Cell B-5 that does a conditional test..(I'm assuming you are using XL 2010 version with the top ribbon controls) Go to the "Home" tab, right of center you will see a icon for "Conditional Formatting". Select cell B-5 on your 'part_tmpl' sheet. Then click on Conditional Formatting Icon. Choose "Highlight Cell Rules" and then --> "Equals To" You will get a new box, in the left half for "Format Cells that are equal to" . Notice there is a blinking cursor there. Use your mouse to select your tab "Master" and and then select cell 'B-5'... when you do this the display will now show " =master!$B$5 ". On the right side of that box choose Green Fill with Dark Green Text. Don't stress too much on the choices, you an easily edit these rules later. Click OK. Now play around with the value of B-5 on both master and part_tmpl to see how this stuff works.

Finish formatting the rest of the "part_tmpl" sheet. When you are through, you can copy the entire sheet, and do a paste special --Format to each of your individual participant sheets. Success, all done.

If for any reason you want to modify the conditional formatting, choose the Conditional Formatting --> Manage Rules selection. You can easily make changes there.

If you wanted to get fancy, you could figure out green == good selection, null formatting == game not yet played and red = OOps, your team didn't win.

Good luck.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.