Dynamically refer to another workbook inside cell of a given workbook

Question

I have a workbook wkb1 having a cell containing a data validation list based on some column array (of names let's say) in a another workbook wkb2. Next to this columns are many other columns with data (let's say ages, birthdays etc) corresponding to these names. In wkb1 I get these data by =OFFSET([wkb1]Sheet1!A3, MATCH(...), colsindex) fomulas, in various cells.

Now, imagine I have a ton of workbooks of the same kind that wkb2, with only different data (different names, ages, birthdays). What I would like to do is the following : in some cell in a sheet of wkb1 I would input the path to one of the wkb2's, and then in all cells (in wkb1) where I have the aforementioned formulas plus the cell containing the validation list, I would like wkb2 to be the one pointed by the path.

Answer 1

I don't believe it is possible to use INDIRECT on a string that includes the path to a workbook.

Using Mac Excel 2016, the following works directly in a cell:

{='/Users/xxxx/Desktop/[Book1.xlsx]Sheet1'!$A$1:$B$2}

but

{=INDIRECT("'/Users/xxxx/Desktop/[Book1.xlsx]Sheet1'!$A$1:$B$2")}

gives #REF!

Without the path, both work:

{='[Book1.xlsx]Sheet1'!$A$1:$B$2}

{=INDIRECT("'[Book1.xlsx]Sheet1'!$A$1:$B$2")}

(although INDIRECT is volatile, while the first version is not)

There was a suggestion on the web somewhere, that defining a name as follows would work: In some cell, $H$6 say, put the path string (for example, '/Users/xxxx/Desktop/[Book1.xlsx]Sheet1'!$A$1:$B$2)

Using Insert > Names > Define Name, create a name, say ExternalRange, with the following value: =evaluate(Sheet1!$H$6)

In the workbook, =ExternalRange is supposed to give you the external workbook values. For me, this only worked if the string in $H$6 did not contain the path. In other words, exactly the same behaviour as INDIRECT. (Note that putting a string into a cell which begins with ' requires an extra ' at the beginning. I don't think this is the root of the problem though, as I observe the same behaviour typing the string in directly, which avoids the need for the extra ')

For your problem, I would try a different approach in any case. Both OFFSET and INDIRECT are volatile, and recalculate every time the worksheet recalculates, rather than only when their inputs change. This has the potential to create a big performance penalty.

To create the Validation that is populated from a separate worksheet, I would use Names which are defined with INDEX and MATCH. I give an example at the end.

To be able to dynamically change the source workbook that contains the validation information, the approach I would take would depend on:

(1) Do you need to use the values from numerous wkb2's at the same time? or (2) Do you need to be able to dynamically switch between various wkb2's, but only use the data from a single wkb2 at any one time?

Also, is the set of wkb2's known beforehand? Or could the user type in an arbitrary path to a wkb2?

Depending on the answers to the above, I would either created a bunch of links (via Names) to the complete set of possible wkb2's (assuming the set size is not too large) and then dynamically change which Name my validation points to (possibly using CHOOSE, again via a Name), or I would change the source of a single link as required. I only know how to change the source of a link using the interface (Data > Edit Links... > Change Source) or via VBA (Workbook.ChangeLink Method). Note that the Edit Links... menu item only appears if you have links.

If I had to take this approach, I would opt to put the path to the source in a cell, provide a button which links to VBA and makes the change, but I guess it would be possible to have a custom VBA function that takes the path and changes the link. Personally, I think using the custom VBA function would be ugly. Even using the button linked to VBA isn't great, so I would choose to predefine all the possible links if at all possible. (which I would probably do with VBA, but this would be VBA that is used once and not part of the final spreadsheet.)

Here is the way I would setup the validation to avoid using OFFSET or INDIRECT, and also to make it easier to change the validation data in the future.

(1) In wkb2, I have the following data in cells $A$1 to $C$5:

Name    Gender  Age
Peter   Male    23
Sally   Female  21
Roger   Male    34
Abad    Male    27

(2) In wkb2, define the following Names using Insert > Name > Define Name, (or with VBA if you are doing it a lot):

Header1 =Sheet1!$A$1:$C$1 (The Sheet1! makes it a local name)
Data1 =Sheet1!$A$2:$C$5 (The Sheet1! makes it a local name)

I prefer to use local names that are defined on a particular worksheet only, which makes it easier if the sheet is duplicated in the same workbook. They do require you to reference the name with the sheetname though (so Sheet1!Data1 rather than just Data1). Global names will work in this example too, if you prefer them.

(3) In wkb1, define the following name:

NameValidation =INDEX([wkb2.xlsx]Sheet1!Data1,,MATCH("Name",[wkb2.xlsx]Sheet1!Header1,0))

NameValidation now refers to the "Name" column in Data1 of wkb2. You can resize Data1, insert new columns, change the order of the columns, etc., and NameValidation will still point to the "Name" column. Alternatively, you could also define your names in wkb2 to be columns only. So a name for "Names", a different name for "Gender", etc. This would be a good approach if the data lengths are different, or the data was not positioned next to each other on wkb2.

(4) In wkb1, use Data > Validation > Settings: Allow List and choose the source as =NameValidation (Note that I don't think it is possible to put the formula =INDEX([wkb2.xlsx]Sheet1!Data1,,MATCH("Name",[wkb2.xlsx]Sheet1!Header1,0)) directly into the validation. I believe you need to go via a name such as I did with NameValidation)