Dropdown lists using each value only once

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?
1
Utf
12/14/2009 5:32:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

5 Replies
4724 Views

Similar Articles

[PageSpeed] 58

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)

-- 
Biff
Microsoft Excel MVP


"bcbrown7" <bcbrown7@discussions.microsoft.com> wrote in message 
news:A932BE44-CFC7-480F-A395-C5CCC6B94017@microsoft.com...
> 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? 


4
T
12/14/2009 6:36:11 PM
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


"T. Valko" wrote:

> 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)
> 
> -- 
> Biff
> Microsoft Excel MVP
> 
> 
> "bcbrown7" <bcbrown7@discussions.microsoft.com> wrote in message 
> news:A932BE44-CFC7-480F-A395-C5CCC6B94017@microsoft.com...
> > 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? 
> 
> 
> .
> 
0
Utf
12/14/2009 7:57:01 PM
bcbrown7;586633 Wrote: 
> 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
> 
> 
> "T. Valko" wrote:
> 
> > 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)
> >
> > --
> > Biff
> > Microsoft Excel MVP
> >
> >
> > "bcbrown7" <bcbrown7@discussions.microsoft.com> wrote in message
> > news:A932BE44-CFC7-480F-A395-C5CCC6B94017@microsoft.com...
> > > 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?
> >
> >
> > .
> >

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

[url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]

0
p45cal
12/14/2009 9:19:32 PM
I'll give it a shot when I'm home tonight.  I'll let you know. 

Thank you very much!

Brian


"p45cal" wrote:

> 
> bcbrown7;586633 Wrote: 
> > 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
> > 
> > 
> > "T. Valko" wrote:
> > 
> > > 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)
> > >
> > > --
> > > Biff
> > > Microsoft Excel MVP
> > >
> > >
> > > "bcbrown7" <bcbrown7@discussions.microsoft.com> wrote in message
> > > news:A932BE44-CFC7-480F-A395-C5CCC6B94017@microsoft.com...
> > > > 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?
> > >
> > >
> > > .
> > >
> 
> 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
> 
> [url="http://www.thecodecage.com"]Microsoft Office Help[/url]
> 
> .
> 
0
Utf
12/14/2009 10:18:01 PM
Your are BRILLIANT!  It works perfectly.  Thank you so much!!!

Brian

"bcbrown7" wrote:

> 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?
0
Utf
12/14/2009 11:18:01 PM
Reply:

Similar Artilces:

A script to replace a value with another
i want to replace a field with another within the same Contact record. Is there any example that you can show me? i'll doing this to over 5,000 records. i'm using Outlook 2002, personal use. rayswchiu -- Phone: (852) 9258 6600 Email: rayswchiu@gmail.com ...

Using MS Access information in Outlook
I have an MS Access DBase that contains our companies contact information. I want to be able to use that information in an Outlook contact folder. I do not want to import the information into Outlook. I want it to be automatically synchronized. Is this possible? Mikeal ...

How to generate XML file as follow, using XmlDocument
Hello, I have to generate XML file as follow, using XmlDocument. <Company> < Employee EID=3D=94111=94 Description=3D=94 xyz=94> <Dept DeptNo=3D=94D10=94> <FirstName> Abhijit </FirstName> <LastName> Babar </LastName> </Dept> <Dept DeptNo=3D=94D20=94> <FirstName> Sahine </FirstName> <LastName> Ghavane </LastName> </Dept> </Employee> < Employee EID=3D=94222=94 Description=3D=94 abc=94> <Dept DeptNo=3D=94D10=94> <FirstName> Swapnil </FirstName> ...

How can I default a lookup field to a particular value.
How can I default a lookup field to a certain value, but still allow the user to modify the field if necessary? Thanks, Bob Johnson you mean a drop down or picklist? "Bob" <rjohnson@reveregroup.com> wrote in message news:1130947400.655495.115390@f14g2000cwb.googlegroups.com... > How can I default a lookup field to a certain value, but still allow > the user to modify the field if necessary? > > Thanks, > > Bob Johnson > i have the same question in crm 3.0. i want a default value in a lookup filed. i also want to create a lookup field by my self....

PatchFactory 2.0 Released. Professional and easy-to-use patch building tool for Win9x/Me/NT/2000/XP.
Description: Professional and easy-to-use patch building environment that can help you to create instant patch packages for software and file updating. Generated patch packages are small size self-extracting executable update programs in a famous installer style with adjustable user-friendly interface and multilingual support. Enhanced with features like easy-to-use interface including a Wizard mode, powerful patch engine, integrated compression technology, adjustable multilingual user-friendly interface of the update program, this program will most definitely become a valuable asset for softw...

x values and plotting points
I need to plot points and draw a straight line to calculate the slope. I will use this same method to present a trend for data points over time ( three years per compound)! Help! I have Excel 97. Thanks! annette christian wrote: > I need to plot points and draw a straight line to > calculate the slope. I will use this same method to > present a trend for data points over time ( three years > per compound)! > > Help! I have Excel 97. > > Thanks! You should be able to chart your data points using the Chart Wizard. See http://www.geocities.com/jonpeltier/Excel/...

Why doesn't indirect work with a dynamic range created using offse
Hi Has anyone else experienced this issue or I am doing something wrong. I have a series of dynamic range names defined using offset. For example the name USCities is defined as =offset(AA1,0,0,Counta(AA:AA),1) and the name Europeancities is defined as =offset(AB1,0,0,counta(AB:AB),1) In column A, dataentry into the cells is limited to USCities or Europeancities. In column B, I want to limit data entry to the range name appearing in column A. For example if USCities is selected in A1, data entry in cell B1 is to be limited to those cells forming the USCities range name. I have tri...

Value creation failed at line 422
Does anyone know the source of this error message and how to fix it? well, you have to provide more info than that before people here could provide possible solutions. Info like, what GP application? what window? what module? what are trying to do on that window? posting? printing? saving? do you have customizations? ---Darryl Bajaro "Phil" wrote: > Does anyone know the source of this error message and how to fix it? ...

Create Outlook 2003 Rule using 'Or' operator in 'Sent to' condition
How do I create a rule that uses the 'or' operator in the 'sent to' condition? I actually have a rule like this, but don't know how I created it. In other rules, when I try to add more that one email address or group in 'sent to,' Outlook inserts the 'and' operator. What is the exact rule in full? In general you'd create additional rules but it depends a bit on the condition or action that you are using. -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins ...

Same contact list on shared computer
My wife and I share the same computer as different users. We are using Windows 7 with Outlook 2007. The question is how did my contacts get shared or replicated with my wife's contacts? When she was looking ups someone she was suprised when all my contacts were also listed. You haven't told us anything that would permit an answer. Sounds to me like you're using the same Outlook data file. Are you? State how you configured each Outlook profile. -- Russ Valentine "JohnR" <JohnR@discussions.microsoft.com> wrote in message news:9550B7CB-DEC8-4D1C-A9FF-5...

making a sum outcome negative based on adjacent cell value
I have 3 columns of single figures. At present i'm using the sumproduc fuction to multiply and total the figures in column A that fall betwee 4 and 9 with the adjacent figure in column B... =SUMPRODUCT(--($A1:$A600>=4),--($A1:$A600<=9),($B1:$B600),($A1:$A600) I'd like to add column C to the formula, so that if it contained value of -1, 1 or 2, the sum of the adjacent figures in columns A and appears as a negative number. For example A3= 7, B3= 2, C3= 1 Outcome= -14 A4= 9, B4= 1, C4= 5 Outcome= 9 A5= 3, B5= 2, C5= 2 No sum because figure in column A ...

where do I find a shortcut lists for international characters
Is there a quick printout I can access in order to use spanish characters in my typing without having to do lots of special set ups? At my school we use a software satelite service and accessing the control panels and such is a non-existing concept for me. I am using Office 2003 Publisher. Insert, symbol List here http://tlt.its.psu.edu/suggestions/international/bylanguage/spanish.html -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "The Spaish Teacher" <The Spaish Teacher@discussions.microsoft.com> wrote in messa...

Permission to use object denied.
I have a user on a Windows 2000 machine with user rights. The user is also running office 97. When he opens a excel speedsheet and Enable's Macros, he gets the following message; "Permission to use object denied". Hi Shannon............ It sounds like there is some sort of link to another workbook that a macro is calling and your user does not have rights to it. I think I would open the book without enabling the macros, and then take a look at them to see if one is trying to do such a thing. hth Vaya con Dios, Chuck, CABGx3 "Shannon" <Shannon507@aol.com>...

Accessing a linked file in a Word document using VBA
Hi - Basic question, in my Word template I have some linked images. I want to use VBA to change the path and filename of some of these links ... can anyone give me a clue as to how I should best do this? Thanks Robin Hi Robin, To see how to paths in Word using vba, check out the macro solution I've posted for implement relative paths at: http://lounge.windowssecrets.com/index.php?showtopic=670027 -- Cheers macropod [Microsoft MVP - Word] "Robin" <Robin@discussions.microsoft.com> wrote in message news:7EA2C548-7D01-4515-BCC8-2EF3210439C9@microsof...

Report WRITER legends values #2
Hello. I need to modify existing modified report. Where can I find values for legends fields? Thanks. Sofiya. Hello Sofiya, Legend fields are placeholders that may contain pretty much anything, depending of the report you are working with. For example, on many reports, they are used to display the range values used when generating the report. (From-To) One thing you must remember though is that they are always typed as strings so you need to convert them if you want to do any arithmetic operations with them. In conclusion, if you want to evaluate the legend field values for a report,...

Data validation causing problems when using a data form in Excel 2
I have an Excel 2007 workbook that includes data validation set on a number of cells. When using a data form to enter data and I enter an invalid value on the form field corresponding to one of those cells I receive the validation error dialogue that prompts me to retry. I enter the correct data into that field on the form then close the form. My worksheet only has the data relating to the corrected field entered. All other data entered via the form is not entered onto the worksheet. Is this a bug in Excel 2007? I previously was using Excel 2002 and found that when using a ...

array formula values
How to get values obtained by an array formula in a single cell separated by commas or any other symbols? Can we sum up or count the values obtained by an array formula in a single cell? The answer to your second question is yes. Simply wrap the array formula witha SUM() function, and commit the entire formula with Ctrl-Shift-Enter "TUNGANA KURMA RAJU" wrote: > How to get values obtained by an array formula in a single cell separated by > commas or any other symbols? > Can we sum up or count the values obtained by an array formula in a single > cell? ...

Collecting Range Name values to VBA
I have a worksheet "Setup" where users type in a date in a cell named "ChtDte" and a path and database name in a cell named "FLName". I am using DOA to connect to a database and return a record set. The query used "qryCOCostwRates" uses a date paramater. Because this sheet will be used by several users all pointing to the database in different locations, I need to know where they have the database. I need to get the values in these two range names in the setup tab of the spreadsheet so I can connect to the data, and provide a value for ...

Form that changes dafualt value in a table
Hi I made a form that has a text box and a command button, and I want to be able to change the default value in a table, for example the default value in field “price” is $2.00 I want to be able to change it to $3.00 by typing it in my form without have to going to the table design view. So if you got some code for my command button please reply. Thank You! On Dec 13, 8:44 am, Jone <J...@discussions.microsoft.com> wrote: > Hi I made a form that has a text box and a command button, and I want to be > able to change the default value in a table, for example the default value i...

Insert Trigger to Update Value of Column in Inserted Row
I am trying to write a SQL Insert trigger which would populate the value of a column in the inserted row with the value of a column from another database table. The table into which the row is inserted does not have a primary key match with the other database table. The two tables can be related through a join to a third table. "CarlC" <CarlC@discussions.microsoft.com> wrote in message news:834D03E6-84CA-4CF3-9781-12FA18713940@microsoft.com... >I am trying to write a SQL Insert trigger which would populate the value of >a > column in the inserted ...

Using Outlook on a MAC
Does anyone know why I can't forward HTML email with Outlook on my MAC? It is received ok but when I forward it, it goes out as text and all the formatting and images are gone. I'd appreciate the help. ...

Using Exmerge in batch-mode doesn't work
Hi there, I want to "backup" the mailboxes from my Exchangeserver to pst-files with Exmerge. I seem to have the same problem though as more people seem to have with the Exmerge utility but I haven't seen any solutions yet :-(. If I walk through the Exmerge steps using the GUI everything seems to go allright. I get a list of mailboxes, I pick the ones I want to export to .pst en Exmerge does it's thing creating a bunch of perfectly healthy .pst's. When I want to use it in batch-mode using MAILBOXES.TXT containing the mailboxes I want to export, Exmerge logs it can...

Using a MS FlexGrid
Hi, I very new to MFC programming so please bear with me. 1) I have dropped a MS FlexGrid onto a dialog which shows up correctly when I run the app but I cannot seem to be able to edit any cells. I have set the enabled property to true 2) I have have a static label which I would like to change at runtime. For example I would like the label to show the row and column I have clicked on in the grid. How can I do this? 3) Also on the OnKeyUpGrid event I would like to test that the Return key has been pressed and then be able to do something like change the cell backcolor. 4) How ca...

use of Randbetween() in Excel
Hi to everyone. I had 10 columns in Excel. A1 B1 C1 ……J1 ..... … … ……… … … … ……… A50 B50 C50 j50 Each column uses the Randbetween() function to create integers 1-100, that is Randbetween(1,100). Is there anyway to use somehow the function so the integers in every row to be different? (I mean: A1 <> B1<> C1…….<>J1, A2 <> B2 <> C2…..<> J2,……………, A50 <> B50 <> C50 …….<> j50) Thank you. http://...

using the $ to lock formulas
We are using the $ to lock the formula but when columns are inserted the formula still changes. How do we correct this? Thank you for any assistance you can give. Hi one way would be to use something like INDIRECT("A1") to always refer to cell A1. Note: copying this formula won't change the cell reference -- Regards Frank Kabel Frankfurt, Germany frankmlr wrote: > We are using the $ to lock the formula but when columns > are inserted the formula still changes. How do we correct > this? > > > Thank you for any assistance you can give. One way: =I...