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

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

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

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