referencing cell within formula

[biagio85]

=INDEX('\\Pl2nas106\Sharedirs$\Sharedirs2\Business Planning\[Exec_HC_FEBFCST1213.xlsx]Sheet 1'!B:B,MATCH($Q6,'\\Pl2nas106\Sharedirs$\Sharedirs2\Business Planning\[Exec_HC_FEBFCST1213.xlsx]Sheet 1'!$AB:$AB,0))

I want to reference this part [Exec_HC_FEBFCST1213.xlsx] with different options but i cannot extract that part out without the forumla blowing up.

=INDEX('\\Pl2nas106\Sharedirs$\Sharedirs2\Business Planning\&B2&Sheet 1'!B:B,MATCH($Q6,'\\Pl2nas106\Sharedirs$\Sharedirs2\Business Planning\&B2&Sheet 1'!$AB:$AB,0))

That didnt work.. any ideas?

 

[Jeff Meyers]

What's in Cell B2?

 

[biagio85]

I would like for in cell b2 to be this "Exec_HC_FEBFCST1213.xlsx"

 

[Jeff Meyers]

Give this a try (changes / additions noted in red):

=INDEX(INDIRECT("'\\Pl2nas106\Sharedirs$\Sharedirs2\Business Planning\&B2&Sheet 1'!B:B"),MATCH($Q6,INDIRECT("'\\Pl2nas106\Sharedirs$\Sharedirs2\Business Planning\&B2&Sheet 1'!$AB:$AB"),0))

Cell B2 = [Exec_HC_FEBFCST1213.xlsx]
Be sure to include the square brackets

Note that with the INDIRECT function you must have the source / reference file open for it to work (there is an Excel Add-in to get around this limitation).