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!
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.
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
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.