Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Tucson, Arizona, USA
    Posts
    12
    Thanks
    0
    Thanked 1 Time in 1 Post
    When I moved to Office 2010, I opted not to include Access because I had only one application: a database of my CD music collection. I imported by Access database file into Excel and except for losing the pretty data entry forms, the new "table" function of Excel works great for this fairly simple database. The new CD collection workbook consists of two worksheets: the first sheet (I'll call it the "CD" sheet) contains many columns with all the pertinent information from each CD (title, performer, label, etc.). Most importantly for this discussion, each row on the "CD" sheet contains the unique catalog number ("primary key" in database speak) for that CD. The second sheet (I'll refer to that as the "song" sheet) consists of two columns: one for all of the song titles from all the CDs and a second column with the catalog number of the CD on which that song resides. Note: this collection is primarily jazz and classical, so the same song title can occur on many different CDs.

    Now that this database is in spreadsheet form, what I would like to do is add a third column on the "song" sheet which includes the actual CD title, not just the CD's catalog number. I thought it would be easy to use Excel's "Lookup" function to take the CD catalog number from the "song" sheet, cross-reference that number with the catalog numbers on the "CD" sheet, and then copy the CD title onto the "song" sheet. I've tried most every combination of arguments in the "Lookup" (and related) function and haven't been able to automatically add the CD title associated with each song title, based on the unique catalog number.

    Any ideas?

  2. #2
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello - Try the attached - see Songs tab in Col C.

    If not what is needed, Please attach the first 10 rows, or so, from each of your sheets.

    Tim
    Attached Files Attached Files

  3. #3
    New Lounger
    Join Date
    Dec 2009
    Location
    Tucson, Arizona, USA
    Posts
    12
    Thanks
    0
    Thanked 1 Time in 1 Post
    Tim,

    Thank you for the reply and my apologies for taking so long to acknowledge your input. I was just able to get back to working the issue and between your suggestion to use VLOOKUP and quite a bit of experimentation, I finally got the results I was seeking. The "secret" to success was moving the CD "Catalog Number" column (used as the primary key) to the first column on the primary sheet. Any other location and either the VLOOKUP function wouldn't work at all, or it would return incorrect results. It apparently does not matter where the primary key column is on the "dependent" sheet, but apparently it must be the first column on the database sheet.

    There may be a lookup function which does not require specific column placement of the data, but this works, so I'm happy. Thanks again for the response.

    BK Howard

  4. #4
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello BK Howard - That is great that you got it to work. Excel has numerous Database features. You will be pleasantly surprised with what Excel can do with databases.

    You are correct that the primary key is in the first column of the lookup range.


    Quote Originally Posted by BK Howard View Post
    and except for losing the pretty data entry forms, the new "table" function of Excel works great for this fairly simple database.
    In Excel, try out Data>Form to do data entry.

    You might also want to explore Data>Filter>AutoFilter and Data>Filter>Advanced Filter.
    Another great feature of Excel is Data>Pivot Table.

    Tim

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    If you do not want the Primary Key of the lookup range to be the first column then you can use MATCH and INDEX together

    [attachment=89544:Song Info Match.xls]
    Attached Files Attached Files
    Andrew

  6. #6
    New Lounger
    Join Date
    Dec 2009
    Location
    Tucson, Arizona, USA
    Posts
    12
    Thanks
    0
    Thanked 1 Time in 1 Post
    Tim, Thank you for this tip:

    Quote Originally Posted by Tim Sullivan View Post
    In Excel, try out Data>Form to do data entry.
    For those of you who have to play, "Where's that command?" in Excel 2010, the "Form" function must be loaded as a custom ribbon item. The function is found at: File | Options | Customize Ribbon | All Commands| Form

  7. #7
    New Lounger
    Join Date
    Dec 2009
    Location
    Tucson, Arizona, USA
    Posts
    12
    Thanks
    0
    Thanked 1 Time in 1 Post
    Andrew,

    Appreciate this idea as well:

    Quote Originally Posted by Andrew W View Post
    If you do not want the Primary Key of the lookup range to be the first column then you can use MATCH and INDEX together

    [attachment=89544:Song Info Match.xls]
    I had looked at MATCH and INDEX in Microsoft Help as a possible solution, but their example made it less than clear how it was used. Your example was great and made it very clear how to setup that combo-function for my use. Using those functions together (now that I understand the syntax) will come in handy for future database manipulations. Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •