working with 2 workbooks

[Jimmy_B]

Hi Guys,

I am wanting to work with data from 2 seperate workbooks.

Workbook One is a template with data stored to specific cells that
want to access in workbook 2.

Sort of like:
='[TPA v6.1 DEC03.xls]Consolidated'!$D$64

My problem is that 'TPA v6.1 DEC03.xls' is not always the name of th
first workbook.

TPA v6.1 DEC03.xls is the template but when saved by users they ca
name this anything they want.

My question is can the workbook (in this case TPA v6.1 DEC03.xls) be
variable that the user enters.

AND

does the workbook have to be open or can the user be prompted to ente
file name and path to where file is located.

Hope you guys can help me out - just can't get my head around it

Thank

 

[Earl Kiosterud]

Jimmy,

If the file name of the external file is variable, you can either use
INDIRECT, or a macro can build a link in a cell. In the case of the
INDIRECT, the file must be open. With the link, it doesn't have to be.

=INDIRECT(B2)
where B2 contains [TPA v6.1 DEC03.xls]Consolidated!$D$64
=INDIRECT("'[" & B2 & "]Consolidated'!$D$64")
where B2 contains TPA v6.1 DEC03.xls

 

[Jimmy_B]

I think i may not have made myself clear, or my simple brain couldn't
quite grasp the answer

what i'm wanting to do is access cells in a seperate workbook

Example of Workbook One as template:

(A1) Expenses (B1) 0
(A2) Revenue (B2) 0
(A3) Profit/Loss (B3) 0

The user of the spreadsheet will then fill out and save eg.

(A1) Expenses (B1) 500
(A2) Revenue (B2) 1000
(A3) Profit/Loss (B3) 500

This could be saved to any location and given any name ie.
the path and filename is user defined
eg. C:\profit Mar04.xls
or C:\My Documents\profit-loss Mar04.xls

Workbook 2 (a template) also requires the same information as Workbook
one. Using the above example it requires the figures for Expenses,
Revenue and Profit/Loss.

I would like to open workbook2 (the template) and have it ask for the
name and path of Workbook1 so that it can automatically populate the
cells in workbook2. Is it possible for this to work? If so, can you
please help out with a detailed response.

Also - can this happen without workbook1 being open ?
remembering that workbook1 can have any name and be stored anywhere (on
network)