software

Simple question...I am looking for a value within a range of nine cells in a
row. If the value is not present I would like for the value that is not
present (false) to be displayed. I tried a nested if statement, but the will
only work on 7 conditions.

For example: Take your average Suduko game. In Row1, numbers 1-9 can
appear in a random order. If I want to know if the number 1 has been entered
in any of the nine row cells, logically I would say quot;If the contents within
B2:B10lt;gt;1, display 1quot; and repeat in the next cell, quot;If the contents within
B2:B10lt;gt;2,display 2quot; and so on...up to the number 9. This way the person
doing the puzzle will know what entries are left.

B2 B3 B4 B5 B6 B7 B8 B9 B10 B11 B15 MISSING
VALUE
Row1 6 7 9 2 1 5
=formula to check for value

If not present, value is

displays

Corrected Example...

B2 B3 B4 B5 B6 B7 B8 B9 B10 B15 MISSING VALUE
Row1 6 7 9 2 1 5 =formula to
check for value
If
not present, value displays

What do you want to return if the value is present? Nothing?

=IF(COUNTIF($B$2:$B$10,ROWS($B$1:B1))=0,ROWS($B$1: B1),quot;quot;)

copy down

btw one of the MS Excel developers (Dan Cory) has constructed a Sudoko
solver using built in functions

office.microsoft.com/en-us/te...809721033.aspx

another one using code is here (by MVP Andy Pope)

andypope.info/fun/sudoku.htm
--
Regards,

Peo Sjoblom

(No private emails please)quot;antwashquot; gt; wrote in message
...
gt; Simple question...I am looking for a value within a range of nine cells in
gt; a
gt; row. If the value is not present I would like for the value that is not
gt; present (false) to be displayed. I tried a nested if statement, but the
gt; will
gt; only work on 7 conditions.
gt;
gt; For example: Take your average Suduko game. In Row1, numbers 1-9 can
gt; appear in a random order. If I want to know if the number 1 has been
gt; entered
gt; in any of the nine row cells, logically I would say quot;If the contents
gt; within
gt; B2:B10lt;gt;1, display 1quot; and repeat in the next cell, quot;If the contents within
gt; B2:B10lt;gt;2,display 2quot; and so on...up to the number 9. This way the person
gt; doing the puzzle will know what entries are left.
gt;
gt; B2 B3 B4 B5 B6 B7 B8 B9 B10 B11 B15 MISSING
gt; VALUE
gt; Row1 6 7 9 2 1 5
gt; =formula to check for value
gt;
gt; If not present, value is
gt;
gt; displaysThanks much Peo Sjoblom... That did the trick. The links were good too..
they helped me better understand the function..

quot;Peo Sjoblomquot; wrote:

gt; What do you want to return if the value is present? Nothing?
gt;
gt; =IF(COUNTIF($B$2:$B$10,ROWS($B$1:B1))=0,ROWS($B$1: B1),quot;quot;)
gt;
gt; copy down
gt;
gt; btw one of the MS Excel developers (Dan Cory) has constructed a Sudoko
gt; solver using built in functions
gt;
gt; office.microsoft.com/en-us/te...809721033.aspx
gt;
gt; another one using code is here (by MVP Andy Pope)
gt;
gt; andypope.info/fun/sudoku.htm
gt;
gt;
gt;
gt; --
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; (No private emails please)
gt;
gt;
gt; quot;antwashquot; gt; wrote in message
gt; ...
gt; gt; Simple question...I am looking for a value within a range of nine cells in
gt; gt; a
gt; gt; row. If the value is not present I would like for the value that is not
gt; gt; present (false) to be displayed. I tried a nested if statement, but the
gt; gt; will
gt; gt; only work on 7 conditions.
gt; gt;
gt; gt; For example: Take your average Suduko game. In Row1, numbers 1-9 can
gt; gt; appear in a random order. If I want to know if the number 1 has been
gt; gt; entered
gt; gt; in any of the nine row cells, logically I would say quot;If the contents
gt; gt; within
gt; gt; B2:B10lt;gt;1, display 1quot; and repeat in the next cell, quot;If the contents within
gt; gt; B2:B10lt;gt;2,display 2quot; and so on...up to the number 9. This way the person
gt; gt; doing the puzzle will know what entries are left.
gt; gt;
gt; gt; B2 B3 B4 B5 B6 B7 B8 B9 B10 B11 B15 MISSING
gt; gt; VALUE
gt; gt; Row1 6 7 9 2 1 5
gt; gt; =formula to check for value
gt; gt;
gt; gt; If not present, value is
gt; gt;
gt; gt; displays
gt;
gt;

Based on your formula... I was able to figure it out...

i.e. searching for the number 1 in affected row, showing that it was used
and displaying the number if it is not used.

=IF(COUNTIF(B2:J2,1),quot;Usedquot;,quot;1quot;)

Thanks much for your assistance. This group rocks!

quot;Peo Sjoblomquot; wrote:

gt; What do you want to return if the value is present? Nothing?
gt;
gt; =IF(COUNTIF($B$2:$B$10,ROWS($B$1:B1))=0,ROWS($B$1: B1),quot;quot;)
gt;
gt; copy down
gt;
gt; btw one of the MS Excel developers (Dan Cory) has constructed a Sudoko
gt; solver using built in functions
gt;
gt; office.microsoft.com/en-us/te...809721033.aspx
gt;
gt; another one using code is here (by MVP Andy Pope)
gt;
gt; andypope.info/fun/sudoku.htm
gt;
gt;
gt;
gt; --
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; (No private emails please)
gt;
gt;
gt; quot;antwashquot; gt; wrote in message
gt; ...
gt; gt; Simple question...I am looking for a value within a range of nine cells in
gt; gt; a
gt; gt; row. If the value is not present I would like for the value that is not
gt; gt; present (false) to be displayed. I tried a nested if statement, but the
gt; gt; will
gt; gt; only work on 7 conditions.
gt; gt;
gt; gt; For example: Take your average Suduko game. In Row1, numbers 1-9 can
gt; gt; appear in a random order. If I want to know if the number 1 has been
gt; gt; entered
gt; gt; in any of the nine row cells, logically I would say quot;If the contents
gt; gt; within
gt; gt; B2:B10lt;gt;1, display 1quot; and repeat in the next cell, quot;If the contents within
gt; gt; B2:B10lt;gt;2,display 2quot; and so on...up to the number 9. This way the person
gt; gt; doing the puzzle will know what entries are left.
gt; gt;
gt; gt; B2 B3 B4 B5 B6 B7 B8 B9 B10 B11 B15 MISSING
gt; gt; VALUE
gt; gt; Row1 6 7 9 2 1 5
gt; gt; =formula to check for value
gt; gt;
gt; gt; If not present, value is
gt; gt;
gt; gt; displays
gt;
gt;