Coin Spreadsheet Question

Im hoping I can get some assistance here on this and if I have went beyond the scope of this thread and this needs to be posted elsewhere, please let me know.

I want to create a column in my spreadsheet that will hold unique numbers, something 5 - 6 digits long and up if needed. I'm using Linux and thus OpenOffice. I have a few issues though.

Im using Tabs to separate out denominations and some denominations will have two tabs (dollars and quarters for instance to separate out the classic from the newer coins). So my unique numbers need to be unique across the whole workbook. I plan on using that unique number as the filename for the image files for the coins.

I can find all sorts of information on how to do this with VisualBasic in Excel, but I dont believe OpenOffice Calc uses or supports VB (Not sure on that one, havent seen it anywhere). So I either need a way to do this without a Macro or VB (use a forumula or something).

I have, thus far, have been unable to find any information that shows me how to do a unique number across the entire workbook using only a formula. Now if there is a way to do this in Calc with a macro or workbook wide script of some kind, I'm all for that too, I just dont know how to do that with Calc.

Thanks in advance for all your help.

I like your tab numbering idea, I had not thought of that and it takes out some complexity. However, I'm not understanding where you are getting the ending number of 19, or was that just for example.

I like that idea a lot actually. So I think I will at least do that.

why do you want random numbers for picture files? technically it is possible that any random number generator can give a number twice.

Personally I like names that mean something, like what are its contents or when was it taken. IIRC on many digital cameras, the default file name usually has date, time, and sequence, like 110527034201 for the first picture taken today, this minute.

I've been testing a new DB for coins. I just care about the type, mint, if graded what grade, purchase price and current value. The DB I am testing does not use Excel BUT gives you the option of adding picts, Unc, Junk, place to add comments, Grade, who graded( you, TPA), ect. I hope the DB goes full production as it will save a lot of people a lot of time( except putting in all your coins!)

Thanks for your reply Nic. I keep working on this and it seems everytime I think I have something that will work, I keep moving towards more complexity, which I'm trying to stay away from unless its just absolutely necessary.

I came up with what I think will be a suitable naming convention for the file names, which will consist of three main parts.

1. The denomination, which may not be completely necessary. But I figured I would use something like 100C for dollar coins (100 cents), 050C for half dollars, 025C for quarters and so on.
2. Include the mint mark
3. Then a random, unique number.

So in the end you would end up with something like 100C.PP.XXXXXX or 100C.P.XXXXXX. That way at a glance I could tell what the denomination was and the mint. I guess I could take it a bit further and use PP, DD, SS, PR, SP to denote proof coins, and silver proofs.

The problem is tying this all together in the spreadsheet. I know how to use the RAND and RANDBETWEEN function and concatenate so that I get the results I want but I wold rather it just pull it all together automatically. I have a column for the Mint mark but no column for denomination since I think it would be rather silly to have a column with nothing but 100C down it.

The other issue is I dont know how to tell the spreadsheet to skip cells and still include all the afore mentioned stuff. I use rows, for example A1, A2, A3, then I skip A4 as it may be a separating row between the lines. Well when you use the smart fill and input your formula then fill down, it fills every row, even the separating rows. So that is a problem.

I know people reading this would say this guy has just lost his mind and taking this way too far and making way too much of it, and they would probably be right. But I like these kinds of challenges as I learn this way. And sometimes its hard to get someone to go along and work through it and come to a solution for how I envision doing it and then say now that we've done that, lets look at this other way that may be easier than what you are doing so that I get the experience and learning of doing it perhaps a more complicated way but then also the simpler way that may even be more effective.

But thats where I stand with all this so far. I've got the basic frame work done, I just need to come up with how to generate the unique id's for each coin so I have unique filenames for each coin, I've already got the rest done.

I think one solution is to just make an extra sheet and have the denomination there in one column (the 100C) and then in the next column, the random, unique number. The issue I ran into with that is getting the sheet with the inventory list on it to pull from that random, unique number list and then delete the number it uses after it sets it on the inventory list so it cannot be used again.

Again, I'm probably over complicating it, but my thought process is if I get through it and solve then along the way I may learn something that will open my eyes and mind to a simpler way.

You're a pretty smart guy there Fuzzy, I think I will have to give that a try and see how that works out.

I like that part of getting pretty complicated. Very possibly one of the many reasons I keep my Excel spreadsheets to bare minimums. Less if possible.
I've read what has been explained here so far about how to document a collection. I feel somewhere along the line the FUN of this hobby is going to make headaches for many way more than it's worth. Look at what you have so far and stop and think how much you really will need or want that in 40 years.