James Chen: Use Consistent Sheet Names

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.

About these ads

About Bill Jelen

Author of 31 books about Microsoft Excel. View all posts by Bill Jelen

One response to “James Chen: Use Consistent Sheet Names

  • Sam Aruti

    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?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 53 other followers

%d bloggers like this: