populating cells with references from another open Excel file

[epayne09]

I have an excel 2003 single-sheet workbook created as a form to print, and need to populate a number of cells with references to cells with another open workbook. The workbook file name will vary, but the cell references within it will always be the same. How do I pass the filename of the other open workbook to the cell references in my form?

 

[Yard]

in it's simplest form:

=INDIRECT("["&B1&"]Sheet1!$A$1")

with the workbook name in cell B1

 

[DonkeyOte]

Welcome to the Board.

Assuming as you say the "target" file is always open you can use the INDIRECT function -- let's say filename ("test.xls") goes in cell A1 on your form and in B1 you want to return the contents from worksheet "DATA" cell "A10" then:

B1: =INDIRECT("'["&A1&"]DATA'!A10")

If you change A1 to be the name of another open file (short name - ie less file path) you should find your values update accordingly.

Notes re: INDIRECT
- as inferred it will only work if the target file is open
- it is Volatile (for more info on volatile functions etc please visit: http://www.decisionmodels.com/calcsecretsi.htm)

Edit: the above is just an extension of Yard's post.

 

[epayne09]

This is not quite what I need. The worksheet name containing the Data is not known to the Excel Form workbook, hence the Data source cannot be found in any cell in the Form file. Within the Form I need a variable reference, for example, to cell D1 from the open Data worksheet. My goal is to have the Data workbook already open, and to click within it on a link that will open the Form worksheet and populate its fields with the information from the fields in the Data workbook.

The Data workbooks are created from a template that will contain links to the Form. So how do I pass the name of the open Data workbook to the Form that will be opened? That is my challenge here.

 

[DonkeyOte]

I'm confused as your last post almost contradicts your first where you say that altough the workbook name is different the cell references are always constant... you are now saying I think that the cell references may be constant but the sheet name is variable as per the file name ? But you then go on to state that within Form book you need:

... a variable reference, for example, to cell D1 from the open Data worksheet

How is this at all possible if you don't know which sheet in the Data file to be the data sheet ?

My initial thinking was that you wanted to pull data directly from the Form by linking into the Data workbook... whereas your latest post implies the opposite to be true namely that you wish to "click" within the Data File so as to push data to the Form... perhaps opening the "Form" in the process ?

Can you possible repeat your question in as much detail as possible to avoid possible confusion ? If the last paragraph holds true I suspect you will be looking at a VBA solution.

Thanks

 

[epayne09]

Please forgive the ambiguity! Here is the situation. We create new Excel 2003 data files every day for funeral arrangements. The structure of these files is identical, so for instance the first name field is always Data!E1 ("Data" is the name of the worksheet in this multi-worksheet file). The way the file is named is "nnnnnnnn lastname, firstname.xls" (the n's are the account number). Each file therefore has a different filename, but the data fields are always in the same place.

So, what I need to be able to do is, with one particular datafile open, for example "20098765 Smith, John.xls" I need to pass this filename as a variable (because it is different for every person's file), as I click a link within it to open my form.

When my form file (also Excel) opens, then, it will take the filename variable and populate its fields with data from that file. The cell reference (Data!E1) will always be the same, but the filename from which the data is passed will be the name of the open file, which as far as the form is concerned, will vary.

To look at it from the point of view of the Form, when the form is open, it needs to know which Data file to use when it pulls the information for its "First Name" field. In that field I need a reference to [variableOpenDatafile]Data!E1. This form will be opened by a link within the Datafile containing the information.

To give an example, I have an Excel form called "VA Form.xls", and this file is NOT open. At 9:00 AM, I have just finished creating the data file called "20091234 Smith, John.xls", and now I am going to click a link within that file to open "VA Form.xls". When this Form opens, I want to see the first name field with the name "John" that comes from the open data file at cell "Data!E1", along with a few other pieces of data. I will then print that form out and close both files.

Then at 10:00 AM, my co-worker finishes creating a data file called "20091235 Jones, Stella.xls", and now he will click a link within that file to open the same "VA Form.xls" that was opened earlier. When the Form opens this time, he needs to see the first name field with the name "Stella" that comes from this open data file.

Etc. Does this help? Thank you! -Ed

 

[epayne09]

By the way, yes, I am sure this is a VBA issue.

 

[epayne09]

Is there an answer to this?