I started fiddling around with a Sudoku spreadsheet this week. I got a Sudoku book in my Christmas stocking. I didn’t have terrible difficulty solving most of the puzzles, but it’s obvious after the first one that Excel could be a nice tool for keeping track of which numbers can and cannot go in certain spaces.
First I downloaded Andy Pope’s Sudoku workbook and the one on Office Online. The former is a masterful piece of coding, but if you’ve ever seen Andy’s work before, you probably won’t be surprised. I plugged in the toughest puzzle from the book, even unintentionally omitting one number, and it solved it in about three milliseconds. The latter workbook was equally intriguing because it used circular formulas and no VBA. It could not, however, solve the super tough puzzle from the book.
I’ll discuss this workbook more when I post it, but on to my immediate problem. I have a range name that contains noncontiguous cells called Grid1. It refers to
and I want to use it in a conditional formatting formula. Apparently
COUNTIF doesn’t cotton to noncontiguous ranges. Juan Pablo Gonzalez says it can be done but his example uses multiple contiguous ranges, whereas I have just one range.
INDIRECT apparently doesn’t like noncontiguouosity either because I couldn’t even get
=SUM(INDIRECT("grid1")) to work, although
=SUM(Grid1) works fine.
The formula I’m trying to enter in conditional formatting is
=OR(ISNUMBER($B$2),COUNTIF(Grid1,B1)>0,COUNTIF(Row1,B1)>0,COUNTIF(Col1,B1)>0). Basically, it means that if there’s a number in B2, a 1 in Grid1, Row1, or Col1, then format B1 (which contains a 1). I’m not interested in creating 81 (9×9) unique conditional formatting formulas, but if I can’t solve this I’ll probably write a macro to create the formulas. That should only take about twice as long as doing it manually, assuming I could do it without errors. Any ideas?