Dropdown lists using each value only once

[bcbrown7]

Is there a way to have 34 total dropdowns (in two different colums) that can
each select a number 1 through 34 but each number can only be used ONCE?

 

[T. Valko]

Yes, you can do this.

Tell us *exactly* where these drop downs will be located. (hint: don't just
say column A and column B. Tell us *exactly* where in column A and column B)

 

[bcbrown7]

That would be great! Here are all of the cells that will need a dropdown:

B7, R7, B13, R13, B19, R19, B25, R25, B31, R31, B37, R37, B43, R43, B49,
R49, B55, R55, B61, R61, B67, R67, B73, R73, B79, R79, B85, R85, B91, R91,
B97, R97, B103, R103

Brian

 

[p45cal]

That would be great! Here are all of the cells that will need a
dropdown:

B7, R7, B13, R13, B19, R19, B25, R25, B31, R31, B37, R37, B43, R43,
B49,
R49, B55, R55, B61, R61, B67, R67, B73, R73, B79, R79, B85, R85, B91,
R91,
B97, R97, B103, R103

Brian

With a non-contiguous range of cells for validation makes things a mite
more difficult. Having played around with this a bit I think I have a
solution, but it may not be especially elegant.
First create a named range of those 34 cells by selecting them, one by
one, while holding down the control key. When done, release the control
key and type:
ValidationRange
into the Name Box (it's usually just at the left end of the formula bar
where you normally see displayed the address of the active cell).
Now add a new sheet.
In cell B1 of the new sheet type in the following formula and press
Enter:
=SMALL(ValidationRange,ROW())
Copy this formula down the column to B34 (You'll see lots of errors
probably.)
In cell A1 enter the following formula:
=IF(COUNTIF($B$1:$B$34,ROW())=0,ROW())
and copy down to A34 (you'll see lots of FALSES and perhaps some
numbers).
In column C1 enter the following formula:
=SMALL($A$1:$A$34,ROW())
and copy down to C34 (you'll see more errors probably).

Now to create a dynamic named range:
First make sure the newly created sheet is active/selected - this is
important.

Pre xl2007 use Insert|Name|Define...
In the topmost field type:
myList
In the bottommost field type:
=OFFSET($C$1,0,0,COUNT($C$1:$C$34))
Click 'Add', click 'Close'

In xl2007:
Go to the Formulas Tab and click on Define Name
Type in the 'Name:' field:
myList
Leave scope as 'Workbook'
and enter this formula in the 'RefersTo:' field:
=OFFSET($C$1,0,0,COUNT($C$1:$C$34))
CLick OK

Now to put in the data validation:
Go to that Name Box again and select 'ValidationRange'. This should
take you to that sheet with all those non-contiguous cells selected.
Bring up the Data Validation dialogue box and in the 'Allow' field
choose 'List', and in the 'Source:' field type:
=myList
then click OK.

With a bit of luck, that should be it. Works here.
I've probably made it more convoluted than it needs to be - it's just
as I developed it - hopefully someone else can come up with something
slicker.

 

[bcbrown7]

I'll give it a shot when I'm home tonight. I'll let you know.

Thank you very much!

Brian

That would be great! Here are all of the cells that will need a
dropdown:

B7, R7, B13, R13, B19, R19, B25, R25, B31, R31, B37, R37, B43, R43,
B49,
R49, B55, R55, B61, R61, B67, R67, B73, R73, B79, R79, B85, R85, B91,
R91,
B97, R97, B103, R103

Brian

With a non-contiguous range of cells for validation makes things a mite
more difficult. Having played around with this a bit I think I have a
solution, but it may not be especially elegant.
First create a named range of those 34 cells by selecting them, one by
one, while holding down the control key. When done, release the control
key and type:
ValidationRange
into the Name Box (it's usually just at the left end of the formula bar
where you normally see displayed the address of the active cell).
Now add a new sheet.
In cell B1 of the new sheet type in the following formula and press
Enter:
=SMALL(ValidationRange,ROW())
Copy this formula down the column to B34 (You'll see lots of errors
probably.)
In cell A1 enter the following formula:
=IF(COUNTIF($B$1:$B$34,ROW())=0,ROW())
and copy down to A34 (you'll see lots of FALSES and perhaps some
numbers).
In column C1 enter the following formula:
=SMALL($A$1:$A$34,ROW())
and copy down to C34 (you'll see more errors probably).

Now to create a dynamic named range:
First make sure the newly created sheet is active/selected - this is
important.

Pre xl2007 use Insert|Name|Define...
In the topmost field type:
myList
In the bottommost field type:
=OFFSET($C$1,0,0,COUNT($C$1:$C$34))
Click 'Add', click 'Close'

In xl2007:
Go to the Formulas Tab and click on Define Name
Type in the 'Name:' field:
myList
Leave scope as 'Workbook'
and enter this formula in the 'RefersTo:' field:
=OFFSET($C$1,0,0,COUNT($C$1:$C$34))
CLick OK

Now to put in the data validation:
Go to that Name Box again and select 'ValidationRange'. This should
take you to that sheet with all those non-contiguous cells selected.
Bring up the Data Validation dialogue box and in the 'Allow' field
choose 'List', and in the 'Source:' field type:
=myList
then click OK.

With a bit of luck, that should be it. Works here.
I've probably made it more convoluted than it needs to be - it's just
as I developed it - hopefully someone else can come up with something
slicker.


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: 558
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=162380

Microsoft Office Help

.

 

[bcbrown7]

Your are BRILLIANT! It works perfectly. Thank you so much!!!

Brian