How do I create variables for linked data?

[montypython1]

Greetings,

I have a master spreadsheet that links data from another spreadsheet. It works great, but it has a limitation.

My problem is that the source of the data (the file location, the workbook, the worksheet, and the cell) is "hard-coded" into the master spreadsheet. I need the source of the linked data to be variable, since I now need to roll this master spreadsheet out to multiple users. Each of these users will have their own data files and folders. My master spreadsheet is called "link_master.xls" and my data source is called "link_data.xls".

Here is what I've tried:

- See line 8 and line 11 below for a typical linked field. This pulls the correct figure from the "link_data.xls" file, since it uses a fixed reference (='C:\Users\Public\Documents\1on1\ExecDoc\01\[link_data_01.xls]data'!$B$11). Unfortunately, it can ONLY pull data from THAT specific file, residing in THAT specific file location.

- See line 9 and line 12 below for my attempt a linking to a variable file location, workbook and worksheet. When I try to duplicate the code in the typical (fixed reference) link, it pulls over the verbiage, but not the value (and I need the value, not the verbiage).

Any suggestions?

Thanks,
Dave


Column AAAAAAAAAAAAAAA Column BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB

5 Data File Location C:\Users\Public\Documents\1on1\ExecDoc\01 6 Data File Name [ ] [link_data_01.xls]
7 Data File Worksheet data
8 Ex: Working Link ='C:\Users\Public\Documents\1on1\ExecDoc\01\[link_data_01.xls]data'!$B$11
9 Ex: Variable Link ="'"&B5&B6&"'!"&B7&B11

11 Field 01 111.01 (using formula in cell "B8")
12 Field 02 'C:\Users\Public\Documents\1on1\ExecDoc\01\[link_data_01.xls]'!data111.01 (using formula in cell "B9")

Column AAAAAAAAAAAAAAA Column BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB

 

[montypython1]

-- continued --

Sorry ... I was going to attach my Excel files, but I'm not sure how to do this in Tek-Tips (the attachment option below seems to only allow attaching from a URL rather than from a local drive).

Thanks again,
Dave

 

[SkipVought]

Hi,
Chech out the INDIRECT function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[montypython1]

Hi Skip,

Thank you for your suggestion. Unfortunately, the INDIRECT function requires the external reference from the other workbook (called "Link_Data.xls") to already be open. Only the master workbook (called "Link_Master.xls") will be open. If the source file of the data is NOT open, then Excel returns the #REF! error value.

It seems like this should be simple, but I just can't figure it out. Any other ideas?

Thanks,
Dave

 

[montypython1]

By the way Skip ... I love your signature line "Just traded in my old subtlety ... for a NUANCE!" Very clever.

 

[SkipVought]

Appreciate you mentioning my pun.

I often reference single pieces of data from other tables, which could be a remote workbook or a database on some network server. I do this with a function that accesses that table (sheet) as a database. I do NOT use a workbook/sheet/cell reference. The workbook is defined in the database connection, the sheet is treated as a table and the cell is defined as a table criteria, like looking up a PhoneNbr for a given name as a criteria.

Is this something that you might be able to use?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[montypython1]

Perhaps this may work, but I was hoping for something simpler (plus, I've not used Excel's database functions before).

Maybe instead of a function, does Excel has some kind of special character (ex: Visual Fox Pro uses an ampersand in front of a variable for their "macro substitution") that would allow the value of the cell to be passed through, rather than displaying the verbiage?

Thanks,
Dave