James Chen sends along this guest post. James is a member of the Excel Gurus group at LinkedIn.
More often than not we need to pull info from various worksheets in a workbook. If you use care to consistently name the worksheets, you can combine VLOOKUP with INDIRECT to pull data from different worksheets. This technique is a life and time saver.
Let’s say you have a sales sheet with various customers and you get a new worksheet every month. Be consistent in choosing a naming rule for the worksheets. For example, you could use Sales_Jan_2012, Sales_Feb_2012, Sales_Mar_2012. This is what I like to call uniform naming.
On a summary sheet, you want to lookup sales for customer XYZ from each monthly sheet. Say that you have true Excel dates for Jan 2012, Feb 2012, Mar 2012 in B3:D3. You have the customer name in A4. The structure of each monthly sales sheet is customer in A and sales in B. Try this formula:
=VLOOKUP($A4,INDIRECT(“Sales_”&TEXT(B$3,”MMM\_YYYY”)&”!$A:$B),2,False)
How it works: the TEXT function formats the date in the format of Jan_2012. The ampersand is a concatenation operator, so you end up passing Sales_Jan_2012!$A:$B to the INDIRECT function. The function will dynamically look at a different worksheet based on the date in row 3.
This is especially useful when you have multiples upon multiples of uniform source data sheets that you need to pull from.
VLOOKUP WEEK 
August 22nd, 2012 at 19:17
Bill,
Great example and one that I use extensively in my workbooks. The problem i have is when I want to access data from a closed workbook. I know INDIRECT fails on closed workbooks. I’ve looked at MOREFUNC and it works great when I need to read1 row but if I want to read all rows it returns a #REF error (e.g K1:N1 works, K:N doesn’t). I’m trying to get details on Harlan Grove’s PULL function but not being a VB person this solution concerns me. What other options are there? Is Microsoft ever going to expand INDIRECT to be able to read closed workbooks?