Merging Excel Sheet with Excel Sheet (Just like in MS Word!)

[Neon520]

Hi everyone,

Purpose: create classroom roster between one Excel spreadsheet as data
source and another excel sheet.

I know how to use one excel spreadsheet as a data source to merge with
another word document; however, by using this method, I found that the link
between the files keep dropping from time to time, especially when the folder
has been moved around.
I'm thinking of creating the same layout of the Word document in another
Excel spreadsheet and somehow "LINK" the data source and the other
spreadsheet together, but I'm not sure how, or whether this is even possible.
I'm thinking of using Vlookup or Hlookup, but this function only return one
result and I'd like the function to return multiple result based on the look
up criteria.

Please find my data source layout below:
First Name | Last Name | Days | Classroom | DoB ...

As you may notice that "Classroom" column will contain repeated information,
and if I choose this as the look up criteria in Vlookup or Hlookup, the
function only return one result. And that's not the whole roster for that
particular classroom.

Is there anyone who have done this sort of thing before?
Is this possible to do in Excel? I'm using Excel 2003.

Thank you,
Neon520

 

[macropod]

Hi Neon520,

Regarding your original problem, Word links to Excel Workbooks will fail if you relocate or rename the Excel Workbooks - you can
move the Word files without problems. Of course, if you move the files to a different PC, the links will fail if any part of the
path to the Excel Workbooks differs. If you're going to be moving the files around on a regular basis, you may find it useful to
employ the macro code I've posted at: http://lounge.windowssecrets.com/index.php?showtopic=670027

 

[Arvi Laanemets]

Hi

To avoid problems with linked workbooks (An Excel file as MM source for Word
is linked too) keep all such workbooks on some shared network resource (on
some server, I'll advice). All users who need to link to such workbooks,
have to map this network resource - preferably all with same drive letter
(p.e. X)

Now, all links to those workbooks will be like
'X:\Sources\[SourceWorkbook.xls]SheetName'!A1 - for every user.
Whenever you migrate this network resource to some new location, all users
have to remap it with same drive letter again, and all links will work
without any problems.


Arvi Laanemets

 

[Neon520]

Hi Macropod,

Thank you for responding to my post.
As much as I'd like to try the method that you suggested, the excel data
file and the word merge file are actually in a single folder on desktop and
never ever moved before, but the link kept broken for whatever reason.

However, the merged files are being access by another user on a separate
computer that have access to that same folder. Do you think it has something
to do with that?

Going back to the solution that I'm trying to do with excel link excel, do
you think it's possible? Any resources you could refer me to?

Thank you,
neon520

Hi Neon520,

Regarding your original problem, Word links to Excel Workbooks will fail if you relocate or rename the Excel Workbooks - you can
move the Word files without problems. Of course, if you move the files to a different PC, the links will fail if any part of the
path to the Excel Workbooks differs. If you're going to be moving the files around on a regular basis, you may find it useful to
employ the macro code I've posted at: http://lounge.windowssecrets.com/index.php?showtopic=670027

--
Cheers
macropod
[Microsoft MVP - Word]

Hi everyone,

Purpose: create classroom roster between one Excel spreadsheet as data
source and another excel sheet.

I know how to use one excel spreadsheet as a data source to merge with
another word document; however, by using this method, I found that the link
between the files keep dropping from time to time, especially when the folder
has been moved around.
I'm thinking of creating the same layout of the Word document in another
Excel spreadsheet and somehow "LINK" the data source and the other
spreadsheet together, but I'm not sure how, or whether this is even possible.
I'm thinking of using Vlookup or Hlookup, but this function only return one
result and I'd like the function to return multiple result based on the look
up criteria.

Please find my data source layout below:
First Name | Last Name | Days | Classroom | DoB ...

As you may notice that "Classroom" column will contain repeated information,
and if I choose this as the look up criteria in Vlookup or Hlookup, the
function only return one result. And that's not the whole roster for that
particular classroom.

Is there anyone who have done this sort of thing before?
Is this possible to do in Excel? I'm using Excel 2003.

Thank you,
Neon520

.