List values NOT entered yet

[foneguy2]

I have multiple columns of 10 rows each.
So the first column has cells A1 through A10.
I need to enter the numbers 1 through 10 in cells A1 to A10 in any
order, one number per cell and use each number only once.
In cell A11, I would like to list the numbers that have NOT been
entered yet.
For example, before anything is typed in cells A1 through A10, cell
A11 would be 1,2,3,4,5,6,7,8,9,10
If I type '4' in cell A1, then A11 would be 1,2,3,5,6,7,8,9,10
That's it.

Thank you!

 

[T. Valko]

Hmmm...

Without using a macro for this, the closest I can get is:

Use a helper column, say, column B. Enter this formula in B1 and copy down
to B10:

=IF(ISNUMBER(MATCH(ROW(),A$1:A$10,0)),"",ROW())

You can hide column B to keep it out of sight if you want.

Then, use this formula in A11:

=SUBSTITUTE(TRIM(B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&"
"&B9&" "&B10)," ",", ")

Biff

 

[Guest]

Maby a UDF in sheet put this:
=Missing(A1:A10)

Function Missing(xrange As range)
Application.Volatile
x = "1,2,3,4,5,6,7,8,9,10"
For Each c In xrange
y = Application.WorksheetFunction.Substitute(x, c.Value, "", 1)
y = Application.WorksheetFunction.Substitute(y, ",,", ",", 1)
x = y
Next
If Left(y, 1) = "," Then y = Mid(y, 2, Len(y) - 1)
If Right(y, 1) = "," Then y = Left(y, Len(y) - 1)
Missing = y
End Function


"T. Valko" skrev:

 

[foneguy2]

Maby a UDF in sheet put this:
=Missing(A1:A10)

Function Missing(xrange As range)
Application.Volatile
x = "1,2,3,4,5,6,7,8,9,10"
For Each c In xrange
y = Application.WorksheetFunction.Substitute(x, c.Value, "", 1)
y = Application.WorksheetFunction.Substitute(y, ",,", ",", 1)
x = y
Next
If Left(y, 1) = "," Then y = Mid(y, 2, Len(y) - 1)
If Right(y, 1) = "," Then y = Left(y, Len(y) - 1)
Missing = y
End Function

"T. Valko" skrev:








- Show quoted text -

Thanks 'excelent' and Valko. Both solutions work great.