Cell reference from within a Vlookup

[ggremel]

I am working with the below formula and have 30 references to maintain. Is there a way to modify this formula to reference the 111 in cell C2 versus manually typing the files in each formula? The key for this workbook is to provide an array function that will permit the referenced files to remain closed.

VLOOKUP(O$7,'S:\Financial Analyst\Budgets\2011 Facilities\Published\[111 2011 Budget Allocation.xls]Facility Budget'!$A$7:$V$500,20,0)

Thank you.

[NBVC]

Unfortunately, to do what you need, you need to employ the use of the INDIRECT function like this:

Please Login or Register  to view this content.

But, the caveat is, that it only works with open source books.

You can however download a free addin from here: Morefunc

and use INDIRECT.EXT in place of INDIRECT in formula above and then you can use it with closed workbooks

[ggremel]

That is precisely what I was afraid you would say. Unfortunately, I would not be able to install this addin onto everyone computer.

Thankyou for the quick reply.

[NBVC]

Note that for the 2003 version, anyway, the addin has a feature that allows you to embed the addin into the workbook so that you can share it and nobody else would need to install it in there own workbook.

After you install it, you should get a menu item in the Tools menu for Morefunc, and from there choose Embed....