Hello everyone 

I've been trying to figure out this for a week now and have come nowhere.

I have to workbooks, lets just call them W1 and W2. I need a formula in W1 that looks for a certain sheet in W2 based on the data in cell B4 in W1. Once the sheet has been found, it has to look for a named range in said sheet in cell B5 wich is in W1.

I haven't found anything yet for this and I am not sure if this is even possible, but if someone could help me with this, I would really appreciate it.   

Thank you. -John

• Moved by Lanlan HuangMicrosoft contingent staff Tuesday, April 28, 2015 1:39 AM

The referred file within INDIRECT must be open.

If you have trouble to create the correct formula syntax:

go into W1 into an empty cell
type a = sign
switch to W2
click into a cell
press Enter

Now you can see the formula in W1, you have to build that string in INDIRECT.

BTW, for closed file and "normal cell references" you can use a macro, have a look here for the DDE function:
http://answers.microsoft.com/en-us/office/forum/office_2010-customize/how-do-i-use-a-variable-for-the-file-name-when/0763d750-d6cc-4f7c-9fb1-c6b0c1de78d0

That did not work for named cells, for that the file must be open and then you can use INDIRECT as shown above.

Andreas.

Thank you so very much, I figured out that both of the files had to be open in the same instance. I had them open apart and thats why it gave me #REF! error.

Thank you 

-John.