Excel 2010 help

Discussion in 'Microsoft Office' started by krneki, May 19, 2012.

  1. krneki

    krneki MDL Novice

    Dec 20, 2010
    22
    2
    0
    #1 krneki, May 19, 2012
    Last edited: May 19, 2012
    I have a document in Excel 2010 which contains 2 columns. Data from 2nd column is constant, does not change and is assigned to data in 1st column. Names and surnames are great example, so I will use this case for explanation.

    Currently there are 200 unique names in 1st column and of course 200 surnames, attached to them in every row. There won't be any new names ever.

    This is functionality I'd like to have. I often have to copy some of those names from external database (software) in various numbers. Let's say 50 random names to 1st column. But when I do that, I have to manually write down surnames to 2nd column, because they come from another database (software) which is not synchronized with the first one, so I cant just copy/paste them.

    Is there any way to save (connect?) all 200 surnames to all 200 names in Excel, so when I would copy some of names from external database to 1st column, Excel would automatically attach appropriate surname to 2nd row?

    I hope I was clear. Thanks for help in advance!
     
  2. krneki

    krneki MDL Novice

    Dec 20, 2010
    22
    2
    0
    #2 krneki, May 24, 2012
    Last edited: May 24, 2012
    (OP)
    Sorry for the delay.

    Here is a simplified example: dl.dropbox.com/u/18516025/excel_example.xlsx . I think it's pretty self explanatory. In the first tab, there is a constant database of all the companies and their assigned numbers. There are going to be several months (2nd and 3rd tabs are just for an example). I explained everything else in the first post.

    Could you do a concrete example of my case and upload is somewhere, so I can take a closer look at it? Thanks in advance.
     
  3. MrG

    MrG MDL Addicted

    May 31, 2010
    855
    201
    30
    Maybe this will help you, it appears that excel will synchronize w/ other documents excel>other document but I cannot tell if it'll do that the other way.

    I looked up synchronized in my “Excel 2010 The Missing Manual.pdf” (896 pgs)

    Embedding and Linking Objects (pg.777)
    Embedding and linking are two tools that let you build compound documents, which
    contain content from two or more programs. Maybe you have a Word file that contains
    an Excel worksheet. You can save this document as one file and print it as one
    document, but you need to use both programs to edit its content. Figure 27-1 shows
    an example.
    Embedding and linking are really two different, but related, concepts:
    Embedding means that a copy of one document is physically stored inside the
    other. If you embed an Excel chart in a Word document, the Word (.docx) file
    contains all the Word document content and all the Excel worksheet data.
    Linking means that one document contains a reference to another document.
    If you put an Excel chart inside a Word document using linking, the Word file
    stores the Excel workbook file’s name and file location information. A linked
    document still stores some of the information from the original source, so that
    you have something to show even if the link gets disrupted (if you delete the
    source file, for instance). However, a linked document’s real power is that you
    can refresh the link whenever you need to—at which point, Excel copies the latest
    information from the source document.
    Both embedding and linking generate the same results visually. Which one you use
    depends on how you plan to update the document. When you embed an object, you
    create a copy of the data inside the new document. You can edit that data separately,
    but it’s no longer attached to the original source document. When you link a document,
    any changes you make to the source document automatically appear in the
    linked document. If you insert a chart inside a Word document, and then modify the
    chart in your worksheet, then the Word document also gets updated.
    Note: Overall, embedding is easier to manage, but linking is your best approach if the source data
    changes frequently and you want to make sure the compound document always has the latest and
    greatest information. Linking also makes sense if you want to keep several documents synchronized with
    the same data (you want to do something like show an important worksheet table in four different Word
    reports). Embedding usually leads to larger files, because more information is placed into one file (the
    compound document that contains the embedded objects).

    HTH
     
  4. MrG

    MrG MDL Addicted

    May 31, 2010
    855
    201
    30
    This is for Office 2007, but should also work similarly on O2010:

    From: "Excel Hacks 2nd Edition.pdf"

    Cross-Application Hacks (Pg. 331)
    Hacks 135–138
    With the ever-increasing usage of computers in our society, it is fast becoming
    necessary for most people to use a combination of applications in their
    work. Microsoft Office 2007’s enhanced capabilities for cooperation
    between its applications makes combining Excel with Word, Access, and
    Outlook easier than before. The hacks in this chapter cover some of the
    most common problems.