I’m trying to build a Sudoku calculator using only Excel (no VBA). As part of the process, I’m using the Position table (see image) as my reference for each cell. If I look at say position 12 in the table, how would I pull column 4 in one formula and row 2 in another formula?
>Solution :
Use AGGREGATE:
Row:
=AGGREGAT(15,7,(ROW($N$5:$V$13)-MIN(ROW($N$5:$V$13))+1)/($N$5:$V$13=12),1)
COLUMN:
=AGGREGAT(15,7,(COLUMN($N$5:$V$13)-MIN(COLUMN($N$5:$V$13))+1)/($N$5:$V$13=12),1)
replace the =12 with = and your cell reference.
But if you use base 9 with two digits, you can just parse the number:
ie:
Now the first digit + 1 is the row and the second + 1 is the column