Excel Sudoku with formula-based validation list

posted Mar 23, 2012, 3:36 PM by r   [ updated Mar 23, 2012, 3:37 PM ]
Our Sudoku tool is a player’s help-tool, not a sudoku solver. The validation list is used to show which numbers could be entered into the selected cell.
At the bottom of the page you can find two files with different formula-structures.

Only formulas are used in the file. The three lines of VBA code are not part of the base solution, but may be helpful.
The soul of the creature is a CELL formula without second parameter. Using it this way: =CELL(“address”) will give back the address of the selected cell once you recalculate after selection change. You can do it by pushing F9 or adding this code to the worksheet module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub

Based on =CELL(“address”) it is easy to determine the sudoku ranges:

- row of the selected cell
- column of the selected cell
- and the 3x3 box around the selected cell.

The numbers in these ranges limit the numbers that could be written into the selected cell. We use a complex array formula to determine these numbers and sort the results in ascending order. Because of the limitations of the validation setting, we need to array-enter this formula into the worksheet. It is not possible to create a named formula resulting in an array and use it in validation. The resulting array always contains 9 elements: #NUM error is returned if the number of possible values is lower. Obviously, we must leave out error values when setting up the validation - we can use an offset formula to limit the range according to the number of values in it. The Offset function gives back a range reference, so it could be used in a named formula in validation. We set up the same validation rule for all the cells in the Sudoku range. According to the selected cell, the formulas will be re-calculated and the validation list of the selected cell will show us the possible values for that particular cell.

By counting the possible values, we can also set up conditional formatting to show how many values could be in a cell.
You may realise that the selected cell itself is included into the ranges determining the possible values. When you manually enter a value into a cell, Excel runs a calculation before evaluating the validation rule. That means in our case you will receive an error message - however the value is correct. To bridge over this difficulty, we must set the calculation to manual. In the attached file, we used the workbook_open and workbook_beforeclose events.

by Frankens Team
Mar 23, 2012, 3:36 PM
Mar 23, 2012, 3:36 PM