Thank you for the further instructions. Your formula worked perfect.
Unfortunately I
did not explain myself clearly. I need the table to be just like the one you
showed me except that when I put the number 1 in cell a1, I do not want it to
appear in row
1 again, etc down to 16. That would mean that I would only need 15 rows
across.
I am sorry for not being clearer. Thank you for your time. jeel

"Bob Phillips" wrote:

I don't know how else to say it, it seems quite clear to me

To force a re-calculation,
- clear cell A1:A16, - just delete the content in those cells
- edit cell B1, don't change it, just edit to reset to 0, - in other words,
F2 then hit return to force it back
- copy B1 across to Q1 and B1:Q1 down to B16:Q16, - self-explanatory
- and re-input A1:A16. - re-enter 1 to 16 in these cells

No idea on the other point I am afraid.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"jeel" wrote in message
...
Thank you for your help. The formula worked well until I got to the part
about
re-calculation. I got to the edit mode but I couldn't figure out how to
get
it to reset.
I am using Excel 2007 with Vista. Also, I more question please. I copied
your note and pasted it into an Excel worksheet. The actual formula won't
copy. I copied just the formula without the equal sign. That worked. This
happens to me every time I
try to copy a formula from the answers. Is there a setting or something I
need to change? Thank you, jeel

"Bob Phillips" wrote:

Here is one way.

First clear the numbers in A1:A16,

First, ensure cell A1 is empty and goto ToolsOptions and on the
Calculation
tab check the Iteration checkbox to stop the Circular Reference message.

Next, type this formula into cell B1
=IF(($A1="")+(AND(B10,COUNTIF($B1:$Q1,B1)=1)),B1, INT(RAND()*16+1))
it should show a 0

Copy B1 across to Q1.
Copy B1:Q1 down to B16:Q16.

Finally, put some the values 1-16 in A1:A16, and all the random numbers
will be generated, and they won't change.

To force a re-calculation, clear cell A1:A16, edit cell B1, don't change
it,
just edit to reset to 0, copy B1 across to Q1 and B1:Q1 down to
B16:Q16then
, and re-input A1:A16.


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"jeel" wrote in message
...
I would like to create a table that is 16 rows deep and 16 columns
across
where the
numbers 1:16 are in the cells in column a. Then going across I need
each
number to appear in each row without the number being repeated in the
column.
Similar to suduko but using 16 spaces. Is there a formula to do that?