Format Cell and Data Validation
 

I'm creating a receipt form that's simple enough for non-Excel expert to use.

There's a column where you enter stuff called BSB number (Bank-State-Branch)
and it is two sets of three digits with hyphen in between (ex. 062-913)

I'd like to format cell so that you type in six digits (ex. 062913) and have
it displayed as 062-913. I'd also like to restrict the entry to six digits
exactly.

With Excel 2007, in Format CellsNumber, I chose Custom and specified Type:
###-###. Also with Data Validation, I put Allow: Text length, Data: equal
to, Length: 6.

This, however doesn't seem to work and when I enter the above example, error
message shows and if you press on, it'll show as "62-913".

How can I make this to work?
--
Maki @ Canberra.AU

Format Cell and Data Validation
 

Try using 000-000 instead of ###-### for you cell format.

--
Rick (MVP - Excel)


"Maki" wrote in message
...
I'm creating a receipt form that's simple enough for non-Excel expert to
use.

There's a column where you enter stuff called BSB number
(Bank-State-Branch)
and it is two sets of three digits with hyphen in between (ex. 062-913)

I'd like to format cell so that you type in six digits (ex. 062913) and
have
it displayed as 062-913. I'd also like to restrict the entry to six
digits
exactly.

With Excel 2007, in Format CellsNumber, I chose Custom and specified
Type:
###-###. Also with Data Validation, I put Allow: Text length, Data: equal
to, Length: 6.

This, however doesn't seem to work and when I enter the above example,
error
message shows and if you press on, it'll show as "62-913".

How can I make this to work?
--
Maki @ Canberra.AU

Format Cell and Data Validation
 

Thank you, Rick.

It does show the first zero now but I still get error message with data
validation.
How can I rectify this?

--
Maki @ Canberra.AU


"Rick Rothstein" wrote:

Try using 000-000 instead of ###-### for you cell format.

--
Rick (MVP - Excel)


"Maki" wrote in message
...
I'm creating a receipt form that's simple enough for non-Excel expert to
use.

There's a column where you enter stuff called BSB number
(Bank-State-Branch)
and it is two sets of three digits with hyphen in between (ex. 062-913)

I'd like to format cell so that you type in six digits (ex. 062913) and
have
it displayed as 062-913. I'd also like to restrict the entry to six
digits
exactly.

With Excel 2007, in Format CellsNumber, I chose Custom and specified
Type:
###-###. Also with Data Validation, I put Allow: Text length, Data: equal
to, Length: 6.

This, however doesn't seem to work and when I enter the above example,
error
message shows and if you press on, it'll show as "62-913".

How can I make this to work?
--
Maki @ Canberra.AU

Format Cell and Data Validation
 

What happens if you change the validation from "equal to 6" to "equal to or
less than 6" (or whatever XL2007's equivalent of that is)?

--
Rick (MVP - Excel)


"Maki" wrote in message
...
Thank you, Rick.

It does show the first zero now but I still get error message with data
validation.
How can I rectify this?

--
Maki @ Canberra.AU


"Rick Rothstein" wrote:

Try using 000-000 instead of ###-### for you cell format.

--
Rick (MVP - Excel)


"Maki" wrote in message
...
I'm creating a receipt form that's simple enough for non-Excel expert
to
use.

There's a column where you enter stuff called BSB number
(Bank-State-Branch)
and it is two sets of three digits with hyphen in between (ex. 062-913)

I'd like to format cell so that you type in six digits (ex. 062913) and
have
it displayed as 062-913. I'd also like to restrict the entry to six
digits
exactly.

With Excel 2007, in Format CellsNumber, I chose Custom and specified
Type:
###-###. Also with Data Validation, I put Allow: Text length, Data:
equal
to, Length: 6.

This, however doesn't seem to work and when I enter the above example,
error
message shows and if you press on, it'll show as "62-913".

How can I make this to work?
--
Maki @ Canberra.AU

Format Cell and Data Validation
 

Yes, this did work!

Thank you, Rick!
--
Maki @ Canberra.AU


"Rick Rothstein" wrote:

What happens if you change the validation from "equal to 6" to "equal to or
less than 6" (or whatever XL2007's equivalent of that is)?

--
Rick (MVP - Excel)


"Maki" wrote in message
...
Thank you, Rick.

It does show the first zero now but I still get error message with data
validation.
How can I rectify this?

--
Maki @ Canberra.AU


"Rick Rothstein" wrote:

Try using 000-000 instead of ###-### for you cell format.

--
Rick (MVP - Excel)


"Maki" wrote in message
...
I'm creating a receipt form that's simple enough for non-Excel expert
to
use.

There's a column where you enter stuff called BSB number
(Bank-State-Branch)
and it is two sets of three digits with hyphen in between (ex. 062-913)

I'd like to format cell so that you type in six digits (ex. 062913) and
have
it displayed as 062-913. I'd also like to restrict the entry to six
digits
exactly.

With Excel 2007, in Format CellsNumber, I chose Custom and specified
Type:
###-###. Also with Data Validation, I put Allow: Text length, Data:
equal
to, Length: 6.

This, however doesn't seem to work and when I enter the above example,
error
message shows and if you press on, it'll show as "62-913".

How can I make this to work?
--
Maki @ Canberra.AU