Hi there Hope,

I'm a record collector and I need to get my collection insured. For many years I worked on cataloging it all and have them listed in two separate Excel sheets in the same file/workbook. I'm hoping to ENLIST your help with merging (or is it syncing, or consolidating? I don't know?) my LPs sheet and 7" SINGLES sheet into ONE master worksheet, preferably sorted by column header ARTIST, then YEAR.

The idea is that when I enter a newly purchased LP or 7" into its respective sheet by inserting a new row, it will automatically populate into the master, RATHER than me being required to remember to copy and paste it in. Likewise, were I to (for example) change the VALUE (listed in $$$ in a cell) of any given record in the LP or 7" SINGLES sheet, it would automatically change in the master.

I have spent 20+ hours (maybe a lot more, I don't know, have lost track), tried various methods and all have failed. The closest I got was using a MICROSOFT Help page:

http://www.rondebruin.nl/copy2.htm

I've tried using this, followed the instructions to the letter etc, all to no avail:
http://www.rondebruin.nl/code.htm


Here's the Microsoft page that I was directed to and think I got close to successfully completing the task!
http://msdn.microsoft.com/en-us/library/cc793964.aspx

Now I'm turning to you for help! I hope you don't mind!

I'm not sure, but it may be relevant that some of the cells have a CATALOGUE # (and maybe even LABEL) that starts with a zero or other number-only combinations. In the document, the cell would not accept what I entered until I changed the cell format from General to Text. I have read in some areas that all cells must be formatted as "General" but I haven't tried this yet.

All column headers in both sheets are identical, and include a SUM function in the final two columns to keep track of COST and VALUE (ie a repurchase price) The SUM function can be removed if that is creating the problem (I have the total $$$ value being SUM'd at the end of all entries) but it would preferable to keep this.

I'm on Windows XP, using Microsoft Excel 2002

Please help!

Best regards,
SnapsDoes your version of Office have Access (database)?  If so, are you familiar with Access?  Achieving what you want would work quite well with Access. Just checked and yes, I do have Access. Sadly, I have NEVER used this program!

But, if you are convinced it will work best for what I need to do, I'm prepared to give it a go.
That is, as long as I am able to import all the data I have entered into my Excel worksheets (the entering of my record collection has taken 8-9 YEARS to complete!)

Thanks SoybeanYes, Access can import from Excel.  Your LPs sheet and 7" SINGLES sheet would each become a "table" in Access.  Then, you could create another table, Query, or Report.  The third table, or the Query or Report form would pull the data from the main two tables.  Therefore, any updating of the two main tables flows into the other table or the Query form or Report form.

Clearly, using Access will REQUIRE some time to get to know enough about it to use it for your record collection.  If you can get a book or video on Access from a library, that would be quite helpful.  Of course, many references can be found on the Internet, such as Creating a new Access database from an Excel spreadsheet.  My search phrase for finding this was import excel sheet into access, or specify the 2002 version and use import excel sheet into access 2002. 

Many books on Access can be found on Amazon.com.  I really don't know what book might be the best to recommend.  For as little as $4, you can get a used copy of Microsoft® Access Version 2002 Step by Step (Step by Step (Microsoft)) [Paperback].  Buyers reviews posted on Amazon.com are mixed.  Another book that can be purchased at very low cost by choosing a used one is Teach Yourself Visually Access 2002 [Illustrated] [Paperback].  Buying more than one book can be a good approach, since the strengths in one may offset the weaknesses in another.

By the way, I hope you have all your record collection data backed up on a medium other than your hard drive.  If not and your hard drive failed, I'm sure you'd be very distressed.  Quote from: soybean on November 06, 2011, 09:45:35 AM

Yes, Access can import from Excel.  Your LPs sheet and 7" SINGLES sheet would each become a "table" in Access.  Then, you could create another table, Query, or Report.  The third table, or the Query or Report form would pull the data from the main two tables.  Therefore, any updating of the two main tables flows into the other table or the Query form or Report form.

By the way, I hope you have all your record collection data backed up on a medium other than your hard drive.  If not and your hard drive failed, I'm sure you'd be very distressed.