data validation

AI Training: For Busy Decision Makers & Professionals Book now

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Data validation

Data validation

resolvedResolved · Urgent Priority · Version 2013

Kathy has attended:
No courses

Data validation

working on database for church choir music; original DB was numbers 1-814. Decided to use 25xxx numbers to denote Christmas, and 35xxx to denote other music, etc... Have gone back in and added two 0s before earlier numbers (ie, 00814) to make them all the same. I am not a pro at this, have done basic DBs, but that's it. Problem: all numbers change over from 001 to 00001 all the way up to 781, then it will not change. I searched online manuals, discovered data validation, and tried putting data validation into that column to make all numbers be 5 decimals- it works until I get to 781, then when I try it, it reverts back to the 3 decimal number. never seen anything like it. I tried to see if I had locked something, but it isn't locked. I can't make this too complicated or other volunteers won't be able to sort, etc- must be easy. Can anyone figure out what is going on? I have tried this with Mac's Numbers, and also online with Google's My Drive (more like Excel), and it happens in both Apps. I don't have Excel, but they are similar enough that it should transfer and I should be able to figure out on the google drive or Numbers. Would really appreciate any thought anyone might have!!!

RE: data validation

Hello Kathy,

Thank you for your interesting question. Just to clarify, do you only need to have two zeros in front of all numbers (e.g. 001 or 00213) or do you also need to have all numbers as 5 digits (e.g. 00001 or 00156)?

* Please note that STL doesn't provide training on Numbers or Google Sheets, only Excel.

Kind regards
Marius Barnard
STL

RE: data validation

I only wanted to put 2 zeroes in front of the lower numbers, ie 003, so that all numbers would have 5 digits.At one point when I sorted, it would mix them up, like 247, 248,249,250, 25001,25002 (going through all the Christmas music with 250xx-which is 25 and 3 more digits- before going back to 251). I thought that I would have to have all numbers consistently having 5 digits for it to sort properly. But why these numbers between 781 and 814 will not let me add the two 0s has me stumped. If there is a way to sort with the three digit numbers always coming before the 5 digits, that would work also, I will just take the 2 preceding zeroes out.
I realize that y'all don't do Numbers or Google, but I figured if I used Numbers and exported as Excel, or used Google Drive, which looks like Excel, that more people could use it- any help at all would be great!!

RE: data validation

Hi Kathy,

In Excel, to have all your numbers 5 digits long, whether it is 1 or 25321, select all the numbers, then right-click the selection. In the options, click "Format Cells".

The "Format Cells" dialog box opens, where you need to select the Number tab, if it isn't already selected. In the Category list, select Custom. You will now see a "Type" field. In this field, type 00000 (5 zeroes).

When you click OK, all your numbers will be 5 digits long. You can sort all these numbers in ascending order and all your Christmas numbers (25xxx) and numbers starting with 35 (35xxx) will group together. So will numbers starting with a zero (these will be at the top).

I hope this helps.

Kind regards
Marius



RE: data validation

Marius, you are an absolute life saver! I looked at data validation and conditional formatting in the Google drive, and found the equivalent- all the numbers appeared as I intended to place them. Thank you ever so much!!! Kathy

RE: data validation

Hi Kathy,

You're very welcome. Glad to be able to help!

Best
Marius

Mon 23 Sep 2019: Automatically marked as resolved.

 

Training courses

 

Training information:

See also:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Brighten up your Excel 2010 Spreadsheet by changing the colours of the gridlines

Excel 2010 allows you to change the colour of grid lines instead of keeping them in boring black.

Select the File tab on the Ribbon, click Options, click Advanced, scroll down to ''Display options for this worksheet.'' Next to ''Gridline colour,'' choose your favourite colour, then once you've done this, click OK. Easy!

View all Excel hints and tips


Server loaded in 0.12 secs.