I have 3 workbooks - Parent1.xlsx and Child.xlsx

Parent1.xlsx has data that will be referenced by Child.xlsx through vlookup.

The vlookup formula is

```
=VLOOKUP(1,[Parent1.xlsx]Sheet1!$A$1:$B$7,2,FALSE)

```

That works fine.

Now I have to make a copy of Parent1.xlsx to Parent2.xlsx.

In order for Child.xlsx to work I have to change the formula to

```
=VLOOKUP(1,[Parent2.xlsx]Sheet1!$A$1:$B$7,2,FALSE)

```

That is ok if its just for 1 cell, but I need to do it for many cells.

To fix this, I plan to used a named range for the file name. So in Sheet2 of Child.xlsx, I have a named range "parent" that has the name of the file - Parent2.xlsx.

I can't seem to get that to work.

If the value for the named range "parent" is 

```
'[Parent2.xlsx]Sheet1'

```

then I'm trying to get 

```
=VLOOKUP(1,parent!$A$1:$B$7,2,FALSE)

```

to work.

Is this even possible? Other than copy pasted everything and using VBA, is there another possibility?

Thanks