I'm stumpped. I have two workbooks, and I'm trying to create a formula that does not require me to type out the full name of the workbook that I'm trying to cross reference. Instead I'm wondering if I can create a table with the list of possible workbook names so that it can auto-update each year. Also, I need help with the same situation with a named range.
Let's say I have two workbooks: workbookA and workbookB. Let's say workbookA is my master database (so the data changes a lot, especially when a new year starts). I use workbookB to reference information in workbookA.
Let's say that within workbookA I have a few named rages called: rangeA1, rangeA2, and rangeA3.
Inside workbookB this is my standard formula:
=vlookup(something,'workbookA.xls'!rangeA1,2,false)
What I would like to do is replace 'workbookA.xls' with a cell vaule that I can update easily. And the same goes for !rangeA1. Or, if I could replace the whole thing....'workbookA.xls'!rangeA1....and reference that to a cell containing that same text, that would work for me too.
I have a whole bunch of vlookup formulas contained in workbookB, and so it would be a lot easier for me to just have one place where I can update the workbook name and/or the named ranges when needed. For example, I often need to reference rangeA2 or rangeA3 quickly. (I know that I can use the Find and Replace tool, but I'd prefer not to if possible. Also, I've tried using the INDIRECT function, but I later realized that it only works if workbookA is open....and I don't want to open workbookA everytime I'm using workbookB).
I hope there's a cool trick out there that I could use! Thanks for reading!
-g/money
Bookmarks