Hello,

I am trying to make an Excel-spreadsheet, based on Sudoku puzzle.

So , cells a1 to i1 have to be filled with the numbers 1 to 9, in random
order.

a1 = empty
b1 = empty
c1 = 4
d1 = empty
e1 = empty
f1 = 6
g1 = empty
h1 = empty
i1 = empty


As you see, a1 to i1 is only filled with 4 and 6.
So the number 1, 2, 3, 5, 7, 8 and 9 has to be filled in, in the others
cells in row 1.

Now I want the following: in column k , I want to see which numbers are
used or not used, so in this example
in row 1, it is 1,2,3,5,7,8,9

The problem is, I want a formula to put on k1, with the following needs:
- check for a1 to i1 if number 1 is on it. In this example NO.
- if NO, then on k1 the number 1 has to appear
- it YES, then on k1 the number 1 has to disappear.

if((a1:i1)=1;" ";"1") is not working. What is the solution ?!?!?!?

If I have 1 formula, I can fill the other cells.

Thanks in advance, for helping me.

Greets,
Wim Rijsdijk

Try this:

Dim myString as String
if((a1:i1)=1,,myString = myString & "1")
if((a1:i1)=2,,myString= myString & "2")
etc.
Then, make place myString in k1

You may have to mess around with the syntax a bit, but this should work.

Wim,

Looks like you are looking for formulas, not programming, so give this a try:

=IF(COUNTIF(A1:I1,1)0," ",1)

Dave

"Wim Rijsdijk" wrote:

Hello,

I am trying to make an Excel-spreadsheet, based on Sudoku puzzle.

So , cells a1 to i1 have to be filled with the numbers 1 to 9, in random
order.

a1 = empty
b1 = empty
c1 = 4
d1 = empty
e1 = empty
f1 = 6
g1 = empty
h1 = empty
i1 = empty


As you see, a1 to i1 is only filled with 4 and 6.
So the number 1, 2, 3, 5, 7, 8 and 9 has to be filled in, in the others
cells in row 1.

Now I want the following: in column k , I want to see which numbers are
used or not used, so in this example
in row 1, it is 1,2,3,5,7,8,9

The problem is, I want a formula to put on k1, with the following needs:
- check for a1 to i1 if number 1 is on it. In this example NO.
- if NO, then on k1 the number 1 has to appear
- it YES, then on k1 the number 1 has to disappear.

if((a1:i1)=1;" ";"1") is not working. What is the solution ?!?!?!?

If I have 1 formula, I can fill the other cells.

Thanks in advance, for helping me.

Greets,
Wim Rijsdijk