| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Obtaining Unique Number From Data Validation List
I have a spreadsheet where I need to obtain a unique number from a
predetermined list for different entries on different rows (these rows are not grouped together or in sequence). I need to pick this unique number sequentially however I cannot use the same number twice. I have tried using a data validation list however this allows the same number to be used multiple times. Is there a method where the number can only be used once? -- Regards Matt |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Obtaining Unique Number From Data Validation List
Create the Validation List on one sheet, but use a formula that watches the Validation Cells on the other sheet and hides the numbers as they become chosen, so they no longer appear in the validation list. Attached is a sheet showing this scenario. +-------------------------------------------------------------------+ |Filename: ReducingValidationList.xls | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=59| +-------------------------------------------------------------------+ -- JBeaucaire ------------------------------------------------------------------------ JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=46886 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Obtaining Unique Number From Data Validation List
There are instructions here for hiding used items in a data validation list:
http://www.contextures.com/xlDataVal03.html Matt wrote: I have a spreadsheet where I need to obtain a unique number from a predetermined list for different entries on different rows (these rows are not grouped together or in sequence). I need to pick this unique number sequentially however I cannot use the same number twice. I have tried using a data validation list however this allows the same number to be used multiple times. Is there a method where the number can only be used once? -- Debra Dalgleish Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Obtaining Unique Number From Data Validation List
Debra Dalgleish;169381 Wrote: There are instructions here for hiding used items in a data validation list: 'Excel -- Data Validation -- Hide Previous Selections' (http://www.contextures.com/xlDataVal03.html) No, that page shows usage of Dependent Lists, lists that completely change to other lists based on prior choices...sequential List Boxes, if you will. The workbook I posted shows a way to ACTUALLY cause individual items to drop out of the Validation List. I'm still looking for a way to cause a validation list to skip the blank spots my technique creates in the list. -- JBeaucaire ------------------------------------------------------------------------ JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=46886 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Obtaining Unique Number From Data Validation List
No, it actually doesn't...
Did you try it? In article , JBeaucaire wrote: No, that page shows usage of Dependent Lists, lists that completely change to other lists based on prior choices...sequential List Boxes, if you will. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Obtaining Unique Number From Data Validation List
JE McGimpsey;169390 Wrote: No, it actually doesn't... Did you try it? My apologies. I've used the Contextures page on dependent lists so many times I just saw that when I brought up the page. This is a way to do exactly what the OP is looking for, a more elegant version of what i suggested above, much more elegant. I'd use this. -- JBeaucaire ------------------------------------------------------------------------ JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=46886 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Obtaining Unique Number From Data Validation List
You can save a few keystrokes in the dynamic range formula by replacing:
COUNTA('Employees - Original Formula'!$C$1:$C$6)-COUNTBLANK('Employees - Original Formula'!$C$1:$C$6) With: COUNTIF('Employees - Original Formula'!$C$1:$C$6,"?*") -- Biff Microsoft Excel MVP "Debra Dalgleish" wrote in message ... There are instructions here for hiding used items in a data validation list: http://www.contextures.com/xlDataVal03.html Matt wrote: I have a spreadsheet where I need to obtain a unique number from a predetermined list for different entries on different rows (these rows are not grouped together or in sequence). I need to pick this unique number sequentially however I cannot use the same number twice. I have tried using a data validation list however this allows the same number to be used multiple times. Is there a method where the number can only be used once? -- Debra Dalgleish Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Obtaining Unique Number From Data Validation List
Even better:
You can replace: COUNTA('Employees - Original Formula'!$C$1:$C$6)-COUNTBLANK('Employees - Original Formula'!$C$1:$C$6) With: COUNT('Employees - Original Formula'!$B$1:$B$6) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... You can save a few keystrokes in the dynamic range formula by replacing: COUNTA('Employees - Original Formula'!$C$1:$C$6)-COUNTBLANK('Employees - Original Formula'!$C$1:$C$6) With: COUNTIF('Employees - Original Formula'!$C$1:$C$6,"?*") -- Biff Microsoft Excel MVP "Debra Dalgleish" wrote in message ... There are instructions here for hiding used items in a data validation list: http://www.contextures.com/xlDataVal03.html Matt wrote: I have a spreadsheet where I need to obtain a unique number from a predetermined list for different entries on different rows (these rows are not grouped together or in sequence). I need to pick this unique number sequentially however I cannot use the same number twice. I have tried using a data validation list however this allows the same number to be used multiple times. Is there a method where the number can only be used once? -- Debra Dalgleish Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Obtaining Unique Number From Data Validation List
Thank you Debra. A bit complicated (for me) but works a treat!
-- Regards Matt "Debra Dalgleish" wrote: There are instructions here for hiding used items in a data validation list: http://www.contextures.com/xlDataVal03.html Matt wrote: I have a spreadsheet where I need to obtain a unique number from a predetermined list for different entries on different rows (these rows are not grouped together or in sequence). I need to pick this unique number sequentially however I cannot use the same number twice. I have tried using a data validation list however this allows the same number to be used multiple times. Is there a method where the number can only be used once? -- Debra Dalgleish Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using automatic Unique Validation list | Excel Worksheet Functions | |||
| DataValidationList - Unique Entries | New Users to Excel | |||
| Unique numbers from data validation list | Excel Worksheet Functions | |||
| obtaining data from a list | Excel Discussion (Misc queries) | |||
| Data Validation using List (But needs unique list in drop down lis | New Users to Excel | |||
Hybrid Mode
