Formula using a named range in another workbook

[SHUTTEHFACE]

Hi all,

I'm trying to use a named range in another workbook inside my index match formula. Because the location of the file will change based on who is accessing it, I have a cell using the CELL("filename") formula to determine the path of the file.This gives me:

C:\Users\SHUTTEHFACE\Dropbox\ABC\ABCdocs\123\[filename.xlsx]Data

I'm then using CONCATENATE to get the proper reference nomenclature for excel:

'C:\Users\SHUTTEHFACE\Dropbox \ABC\ABCdocs\123\[filename.xlsx]Data'!

[filename.xlsx]Data'!

contains a bunch of dynamic named ranges I'd like to use inside my INDEX MATCH Formulas. First off, is this possible? Second, how would I do this?

Cheers,

STF

 

[dUBBINS]

Using CELL("filename") can give unwanted results. Because every time a new sheet is activated it will become that sheet name. I have definitely used it in the past, but just a warning that you have to be mindful of that.

I have never tried what you are attempting, but if you cannot get your solution to work, my only idea is to create a cell off screen. Use this cell for each person to enter the path; not including the file name. And then have the formula use this cell & whatever the filename is. Not sure how many people you are dealing with, or if they are computer literate. But just in case it is helpful. Best of luck to you.

 

[SHUTTEHFACE]

Thanks duBBINS, will let you know how things work out. Can I use CONCATENATE inside the INDEX formula to define the path as I have a number of named ranges named after the title of each column. So I am using:

[COLOR=#333333]=INDEX(CONCATENATE(Cellwithpath,Cellwithrangename),MATCH(.....))[/COLOR]