The cell with data|validation is A1 (but not on sheet1--where the
data|validation list is), right.

You can format this cell as Custom: 0000
to show leading 0's in numeric entries.

But you'll have to change the values in A1:A13 of sheet1 to be real numbers--not
text.

Change the format of that range to General.
then select an empty cell and copy it.
select the range
edit|paste special|click Add


A custom format of: 0000
will keep the leading 0's for numeric entries. It won't have any impact on a
non-numeric entry. (Only numbers are affected by number format.)

I don't know what you mean by that last portion--validation via array function.
(I was guessing you had a named range on that other sheet and were just using
List in Data|validation--but that might not be close!)




cclambie wrote:

Thanks Dave,

That didn't work exactly.

I have tried to get around it this way, but running into a different
issue.

I have formated the cell where the validation is, a1, as custom ####

Then in the array, sheet1(a1:a23), I have formated as text.

Problem lies in the leading zero on the first few records in the
array?

If formated as number, the text brings up errors.
If formated as number, leading zero disspears.

If formated as text, have to add leading ' to make it recognise all
fields in array (onyl recognises fields with '0, which it added auto
when I put it across)

Any ideas on a common format that will
A. have leading zeros
B. allow alphanumeric codes
C. validation via array function will allow all formats?

Thanks heaps

--
cclambie
------------------------------------------------------------------------
cclambie's Profile: http://www.excelforum.com/member.php...o&userid=29135
View this thread: http://www.excelforum.com/showthread...hreadid=488546

--

Dave Peterson