Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I combine spreadsheets and documents in one file?
I'd like to know if it's possible to have Excel spreadsheets and Word
documents all in the same file. For example, one of my employees updates a particular spreadsheet monthly and also sends out a memo with the Excel data. Instead of having two separate files, can they be grouped? Thanks |
#2
|
|||
|
|||
Trish,
If the employee and the recipients are using xl 97 then "Office Binder" provides the functionality you desire. However, the office suite was "improved" in later versions by removing the Binder utility. You could copy the word document and insert it into Excel worksheet as an icon. Then the recipient can double- click the icon to automatically open Word with the document displayed. Jim Cone San Francisco, USA "Trish" wrote in message ... I'd like to know if it's possible to have Excel spreadsheets and Word documents all in the same file. For example, one of my employees updates a particular spreadsheet monthly and also sends out a memo with the Excel data. Instead of having two separate files, can they be grouped? Thanks |
#3
|
|||
|
|||
Trish, the way I combine the 2, if they do not need to be edited by the
recipient, is to create a PDF fi.e. -- Sincerely, Michael Colvin "Trish" wrote: I'd like to know if it's possible to have Excel spreadsheets and Word documents all in the same file. For example, one of my employees updates a particular spreadsheet monthly and also sends out a memo with the Excel data. Instead of having two separate files, can they be grouped? Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I combine spreadsheets and documents in one file?
There is most definitely a better way to do this. This is a quick and dirty way to get the job done. If you're reading this for the first time, it won't sound "quick and dirty". After you do it a few times, it is. This process uses worksheets within the same spread sheet. There must be a common key field (column) in each of the two spreadsheets you want to combine. Paste the first spreadsheet into a new spreadsheet in workbook "Sheet1". I suggest putting the key field in the A column, and sort the sheet in ascending order by this field. If this is a problem, or you need to retain the original sort order and don’t have another column you can sort by that will restore the sort order, I suggest inserting two columns at the beginning of your spreadsheet. In column B, simply put the numbers 1 through however many rows you have. When you are done, you can sort by this column and your original sort order will be restored. Copy and paste the key field column into column A, and sort the sheet by column A. Whatever you use as a key, the sheet must be sorted in ascending order by that key. Not doing so may result in inaccuracies. In whatever column the key exists in, it must be to the left of the fields you want to merge. This is why I suggest copying the column into column A. You can always delete later. Go to "Sheet2". Paste the second spreadsheet here. Again, the key field should be in column A, and the sheet sorted by this column. Follow the recommendations above. This example assumes a spreadsheet in "Sheet1" that is 100 rows long and 4 columns wide (columns A, B, C, and D). Again, column A is the key field, and is sorted ascending. Also assumed is that "Sheet2" is 100 rows long, and 4 columns wide (columns A, B, C, and D) and that column A is the key field, and is sorted ascending. In this example, you want to copy the cells in columns B, C, and D, in sheet2, to sheet1, columns E, F, and G, respectively, for each row , where the cell in column A sheet1, matches the cell in column A sheet2. For example, sheet1, cell A5, matches sheet2, cell A50. So, copy sheet2, cells B50 C50 and D50, to sheet 1 cells E5 F5 and G5. This example will copy the cells for each match. On sheet1, copy the following formula into the designated cell. Sheet1, Cell E1, formula: =VLOOKUP(A1,Sheet2!$A$1:$D$100,2,FALSE) Sheet1, Cell F1, formula: =VLOOKUP(A1,Sheet2!$A$1:$D$100,3,FALSE) Sheet1, Cell G1, formula: =VLOOKUP(A1,Sheet2!$A$1:$D$100,4,FALSE) Copy and past the sheet1 cells from E1:G1 to E2:G100. Your done. This method works best if the source cells are formatted as "TEXT". To keep your results, you'll need to copy and past the results to a plain text word processor, like notepad, to clear the formulas and retain the data. Here's an explanation of the process. =VLOOKUP(A1,Sheet2!$A$1:$D$100,2,FALSE) Part 1: A1 Part 2: ,Sheet2!$A$1:$D$100 Part 3: ,2 Part 4: ,FALSE - Part 1, A2 Search for the value contained in this cell, this worksheet (Sheet1) - Part 2: Sheet2! The place to search is not in this sheet, search in Sheet2. The exclamation denotes that it is a worksheet. $A$1:$D$100 Table array to search. Search the cell range of A1 to D100. Why the dollar sign ($)? This allows you to copy and paste this formula into several cells without excel performing it's logic to change the cell range based on the relative location of the cell you are posting too. In other words, yes I really do mean these cells literally. Don't change them. - Part 3: ,2 This is the number of the column of the cell, that you want to return the results of, relative to your search table array. OK, what does that mean? You searched Sheet2. VLOOKUP automatically searches the first column you specified. That's column A. When it finds a match in column A, it will return the value of the cell you specify here. You searched A1:D100. That's A-D. So, 1 equals return the value of the cell in column A 2 equals return the value of the cell in column B 3 equals return the value of the cell in column C 4 equals return the value of the cell in column D If you had searched D5:J20, then: 1 equals return the value of the cell in column D 2 equals return the value of the cell in column E 3 equals return the value of the cell in column F 4 equals return the value of the cell in column G 5 equals return the value of the cell in column H 6 equals return the value of the cell in column I 7 equals return the value of the cell in column J Part 4: ,FALSE Don't approximate, or return results of the best match. Only return results if the key field matches exactly. Hope this helps. If I would have had this information a long time ago, it would have saved me a lot of time. I suggest performing tests on smaller sheets to verify your expected results before using this on any important data. Here's a link with more information: http://office.microsoft.com/en-us/ex...093351033.aspx -- User282282 ------------------------------------------------------------------------ User282282's Profile: http://www.officehelp.in/member.php?userid=5036 View this thread: http://www.officehelp.in/showthread.php?t=752237 Posted from - http://www.officehelp.in |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Print errors with Word documents in Excel spreadsheets | Excel Discussion (Misc queries) | |||
Users suddenly able to modify the same documents at the same time. | Excel Discussion (Misc queries) | |||
Is there a way to compare 2 spreadsheets with Excel? | Excel Discussion (Misc queries) | |||
Finding Words In Excel Spreadsheets & Word Documents | Excel Discussion (Misc queries) | |||
How can I open documents sent to me as .123 | Excel Discussion (Misc queries) |