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
