We are using Excel 2007.
I am getting a headache trying to figure out how to write a formula that uses Indirect and VLookup to access data stored in a different workbook.
The straight VLookup formula is '=VLOOKUP(AA13,'10BALANCEfor2011cost_CR_Test.xlsx'!UnMetered_Summary2010,2,FALSE)
The AA13 is the cell holding the name of the community that I want to lookup information on.
In the workbook where I want the modified formula there are two range names that hold the following:
Range named "Unmetered" holds source workbook name (10BALANCEfor2011cost_CR_Test.xlsx)
Range named "Summary_Sheet" holds the range name of the Lookup Table_Array (UnMetered_Summary2010)
I want to be able to change the Source workbook name and Lookup Table_Array name each in one place and all the formulas that use this information are updated automatically.
I know both workbooks will need to be open for the formulas to work. I just do not want to have to change every formula when we need to use a new workbook or Table_Array.
Is this possible with this method? Or is there a better way to do it?
Thanks for any Thoughts on the Subject.
Using Indirect with VLookup
-
- 3StarLounger
- Posts: 233
- Joined: 10 Feb 2010, 19:17
Using Indirect with VLookup
Chuck Reimer
(I'm from the Government and I'm here to help) ;-)
(I'm from the Government and I'm here to help) ;-)
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Using Indirect with VLookup
Try
=VLOOKUP(AA13,INDIRECT(Unmetered&"!"&Summary_Sheet),2,FALSE)
=VLOOKUP(AA13,INDIRECT(Unmetered&"!"&Summary_Sheet),2,FALSE)
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 233
- Joined: 10 Feb 2010, 19:17
Re: Using Indirect with VLookup
Hans,
Once again I THANK YOU.
Formulas like this make me crazy. Yours works Very Nicely.
Have a Great day.
Once again I THANK YOU.
Formulas like this make me crazy. Yours works Very Nicely.
Have a Great day.
Chuck Reimer
(I'm from the Government and I'm here to help) ;-)
(I'm from the Government and I'm here to help) ;-)