Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation Data using Validation Table cell range..... | Excel Discussion (Misc queries) | |||
Cell validation = format? | Excel Discussion (Misc queries) | |||
Data Validation or Conditional Format (or combo of) | Excel Worksheet Functions | |||
data validation list drop down text format too small | Excel Worksheet Functions | |||
Custom Format and Data Validation Q | Excel Worksheet Functions |