Hi

Firstly, Hello, my first post! So thanks if you can help

I would like to add a ' to a range of cells to ensure my previous
validation continues working.

Basically. Even though the cells are formated in Text format,
validation isn't working for some reason without the 'xxxx in the
cell.

does anyone know how to add this?

I have tried concatenate, but i then have to go down and f2, enter in
all the cells??

Would really appreciate your help.


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

I don't think that this will help.

If the value is already in the cell, you won't be reapply the validation.

And if you're getting it ready for the next change, then the user could avoid
typing the apostrophe -- so that won't help.

Maybe just formatting the cell as text would be sufficient to catch the next
change--again, it won't help the current value in the cell.

cclambie wrote:

Hi

Firstly, Hello, my first post! So thanks if you can help

I would like to add a ' to a range of cells to ensure my previous
validation continues working.

Basically. Even though the cells are formated in Text format,
validation isn't working for some reason without the 'xxxx in the
cell.

does anyone know how to add this?

I have tried concatenate, but i then have to go down and f2, enter in
all the cells??

Would really appreciate your help.

--
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

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

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

your right about the list function, validation via a named array.

That is the issue.

With the format custom 0000, or #### it allows the leading 0, but the
validation doesn't work?

I need a format that will allow all?


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

alright.

Did what you suggested, with a little extra.

just ran through all the fields with the leading 0 and hit f2, enter to
"reformat" activate or whatever the cell, then it works.


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

ok...this then didn't work...damm it.

so used this code to add the leading apostrophe and it appears to work
now

'Sub fixanzsic()
'*****Currently not in use, to update ANZSIC list to text format

'Dim strg As String

'Do Until ActiveCell.Value = ""

'strg = ActiveCell.Value

'strg = "'" & strg

'ActiveCell.Value = strg

'ActiveCell.Offset(1, 0).Activate

'Loop

'End Sub


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