Index Match with dynamic external reference (closed file)

Hi

i'm italian and my english isnt good.

My search into forum was unsuccesfull.

i'm trying to have a dynamic external reference to closed file sheet in this formula:

=INDEX('C:\MyFolder\[FileFrom.xlsx]Sheet1'!B$11:$D$100,MATCH(A6,'C:\MyFolder\[FileFrom.xlsx]Sheet1'!$B$11:$B$100,0),3)

where in my sheet column B from B6 there are sheets name: Sheet1 - Sheet2 - Sheet3 etc.

There is a way?

Thanks in advance
domenico

(i submit same question to Microsoft italian forum without answer)

Yes, but there is a complication. Will the filefrom.xlsx be OPEN or CLOSED?

If it is CLOSED is a VBA solution OK??

Please post a link to the Italian forum where you asked the question. Thanks.

I have just seen the title of your thread. So - it is closed... Is VBA any good to you?

I was going to suggest a UDF - but it seems that you can already do that.

Aside from that - no, you're stuck. You need INDIRECT to pick up the sheet name and that doesn't work on closed sheets.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.