something better than rand

[bademployee]

I'm looking for a better option than below to come up with 3 digit contract numbers

=INT(1000*RAND())

Ideally I'd like a value returned from a range when a button is hit, each value returned would then be crossed off the list/disabled, so it could not be returned twice.

Possible?

Thanks for any help in advance.

Mark

 

[THEGOB]

Is there a reason that the contract number needs to be random and not say sequential?

 

[bademployee]

no, actually sequential would be better

 

[moonfish]

If you have a list of existing three digit numbers including 0 and 1000, sort them and put them into column A of the spreadsheet you'll be using. Then use the following two formulas:

Excel Workbook
	A	B	C	D
1	000	*	pos	num
2	041	*	8	594
3	155	*	*	*
4	207	*	*	*
5	261	*	*	*
6	337	*	*	*
7	339	*	*	*
8	427	*	*	*
9	632	*	*	*
10	804	*	*	*
11	809	*	*	*
12	1000	*	*	*
Sheet

This might be difficult to manage, though.

EDIT: Bah, I assumed you needed them to be non-sequential.

 

[THEGOB]

Should also have said what happens when you trip past 999?

 

[bademployee]

Wouldn't get that high, normally only use 500-700 contract numbers a year.

 

[bademployee]

Moonfish: my bad

 

[THEGOB]

If you are not restricted I would use 7 digits and have it as year and then contract, e.g. first would be 2011001, then 2011002 etc

This does not need to be generated but can simply be generated as next value from a list. You could code in VBA to automatically assign.

 

[bademployee]

Could do something very similar, I wouldn't have a clue as to what vb code is required

Thanks

 

[THEGOB]

If it's easiest I would just maintain a list of used which would start at 2011001 and then if that was say cell A1 then A2 would be =A1+1

Hope this solves but could code if required.