Linking multiple documents into one master log file.

[marktrosen]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Here is my scenario:

I have 3 types of document. Document A is an inspection report; a standard form into which data is entered on site for each site inspected. Document B is a log file that tracks the data gathered in each document A that is created. Document C is a final report, completed for each site inspected, which is a standard form completed from data compiled in document B.

So, there are several copies of document A, only one document B, and several copies of document C.

What I am trying to accomplish is this: after completing document A on site, I would like to be able to open document B, select a row, and instruct it to fill in the cells of that row with information taken from various locations in the workbook of document A. I would then like to open a template of document C, and instruct it to be completed using data from a row in document B that I specify. I hope that makes sense.

Can anyone help me with this? I am using Office 2008 for Mac, on an Intel based machine. Thank you so much!

 

[Bob Greenblatt]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Here is my scenario:

I have 3 types of document. Document A is an inspection report; a standard
form into which data is entered on site for each site inspected. Document B is
a log file that tracks the data gathered in each document A that is created.
Document C is a final report, completed for each site inspected, which is a
standard form completed from data compiled in document B.

So, there are several copies of document A, only one document B, and several
copies of document C.

What I am trying to accomplish is this: after completing document A on site, I
would like to be able to open document B, select a row, and instruct it to
fill in the cells of that row with information taken from various locations in
the workbook of document A. I would then like to open a template of document
C, and instruct it to be completed using data from a row in document B that I
specify. I hope that makes sense.

Can anyone help me with this? I am using Office 2008 for Mac, on an Intel
based machine. Thank you so much!

Well, no, it does not make much sense. Can you copy the row and paste it
into another sheet? Why does this not work for you? Can you not build in the
links so that they are updated automatically? I think I'm going to need a
more specific example of what you have and what you want before offering a
better solution than this.

 

[marktrosen]

Ok, I'll try to be clearer.

The reason I can't copy/paste lines from one to the other is that the data in Document A is scattered throughout the document (the cells don't all fall in a nice neat row or column). I have no control over this, because it is a form provided for me, and not created by me. Also, I would like them to be linked rather than simply copied so that document B would automatically update if document A changes.

I know how to link individual cells from one workbook to another, but because there are dozens of cells to be linked for each document A being logged, I want to be able to say "get the data from this file, and put it here in this row of document B". Ideally, the row of data created would be compiled from all of the miscellaneous cells in the copy of Document A that I point to.

Any clearer? I know it sounds convoluted, but I think in the end it is actually simple. If it's still too muddy, please let me know and I'll try again.

Thanks for your help.

 

[Bob Greenblatt]

Ok, I'll try to be clearer.

The reason I can't copy/paste lines from one to the other is that the data in
Document A is scattered throughout the document (the cells don't all fall in a
nice neat row or column). I have no control over this, because it is a form
provided for me, and not created by me. Also, I would like them to be linked
rather than simply copied so that document B would automatically update if
document A changes.

I know how to link individual cells from one workbook to another, but because
there are dozens of cells to be linked for each document A being logged, I
want to be able to say "get the data from this file, and put it here in this
row of document B". Ideally, the row of data created would be compiled from
all of the miscellaneous cells in the copy of Document A that I point to.

Any clearer? I know it sounds convoluted, but I think in the end it is
actually simple. If it's still too muddy, please let me know and I'll try
again.

Thanks for your help.

Yes, it is clearer. I do not know of any way to create the links other than
cell by cell. If you have several versions of Document A (assuming they are
in the same format) then you can change between them by using Change Source
in the File-Links dialog. There may be other ways to do this using a file
name in document B and the INDIRECT formula, but you'll have to be even
clearer about what you are trying to do. For example are there many
different document As, each to appear in their own row of Document B? Or do
you want the row in document B to only show a current version of document A?
Since the source cells are scattered throughout document A, you'll have to
build each link manually.

 

[marktrosen]

There are many different versions of document A, each in the same format but with different data. I would like each to appear in their own row in Document B.

 

[Bob Greenblatt]

There are many different versions of document A, each in the same format but
with different data. I would like each to appear in their own row in Document
B.

Then, I suggest you generate a document B that will allow you to place the
name of the Document A in a cell in the row, and then use formulas to
extract the appropriate data. You can then fill these formulas down in
document B for as many rows as you need.

Example in document B:
A B
1 DocumentAName =indirect("'["&A1&"]Sheetname'!Z3")

 

[marktrosen]

That sounds very promising, however I'm not very experienced in excel and can't quite understand how to input your example. Could you please clarify it a little for me? Thanks.

 

[Bob Greenblatt]

That sounds very promising, however I'm not very experienced in excel and
can't quite understand how to input your example. Could you please clarify it
a little for me? Thanks.

Referencing the example in the prior message, put the name of the workBOOK
for a document A in a column in the row of document B where you want
document A's data (I used A1, but you can put this anywhere). Then in the
first location where you want data from document A type the formula.
Substitute Sheetname for the actual SHEET name of Document A containing the
data, and change the Z3 to the cell reference of the source data in document
A. Do this for each cell in document B where you want data from Document A.
When you are done, you'll have all the data you need from document A in a
row in document B. then, you can fill these formulas down to collect data
from other document As, just change the workbook name in each row.

Note that a document A has to be OPEN for its data to appear in document B.

 

[marktrosen]

Great! So now I've gotten that formula to work...but does this mean that each time I open Document B, it will have to open each version of Document A in order to reference the data? Is there a possible workaround for this?

Thanks so much for all of your help on this. I think I'm making great progress...

 

[Bob Greenblatt]

Great! So now I've gotten that formula to work...but does this mean that each
time I open Document B, it will have to open each version of Document A in
order to reference the data? Is there a possible workaround for this?

Thanks so much for all of your help on this. I think I'm making great
progress...

Glad you got it working. If you want the data in Document B to reflect the
most current information in document A then yes, you will need to open
document A, and no there is no work around. If document A is stable and will
not change after the information is in document B, then all you have to do
is replace the formulas with the data. Copy the row containing the document
A data and go to Edit-Paste Special, and select values. This will remove the
formulas so that document A data will be stable.

If you want the option of updating the document B data or not, then this
method will not work, and you will have to resort to actual links for each
cell that links to the cell in document A. then when you open document B you
will get the options to update the links, or not and just use the last set
of data.