Search code examples
excelexcel-formulavlookupworksheet-functionexcel-indirect

Creating a VLOOKUP that references another sheet, but the sheet name is the content of a cell

I'm trying to create a VLOOKUP for billing purposes. I need to have the number of last month's documents in the current month's billing information. I have the VLOOKUP working with no problems, but I want to name the sheet after the month and have the VLOOKUP reference a cell for the sheet name. So far I have: 

=VLOOKUP(A4,INDIRECT(CONCATENATE(B1,"!")),A:G,7,FALSE)

where A4 is Project Name
B1 is Previous month (ie July2014)
A:G is the table array on the previous month's sheet
in which it is column 7 that contains the closing document count.

Solution

  • Please try: 

    =VLOOKUP(A4,INDIRECT(B1&"!A:G"),7,FALSE)

    though you might want to consider using named ranges of Workbook scope.