Solved

# Referencing a sheet via cell

Posted on 2013-06-25
204 Views
I want to  set up a vlookup within a workbook and I want to be able to pick my worksheet based on a date in cell A2.  The range will always be the same but the worksheet I am referencing will be dynamic based on the date in A2.  this is the formula +IFERROR(VLOOKUP(E\$3,'16-Jun-2013'!\$B\$31:\$O\$78,MATCH(\$B11,'16-Jun-2013'!\$B\$3:\$O\$3,0),0),0)
0
Question by:Jenedge73
• 2

LVL 12

Expert Comment

ID: 39276078
I suggest using the Indirect formula with a Text format of the date in cell A2. Try this:
=IFERROR(VLOOKUP(E\$3,INDIRECT(TEXT(A2,"dd-mmm-yyyy")&"!\$B\$31:\$O\$78",MATCH(\$B11,INDIRECT(TEXT(A2,"dd-mmm-yyyy")&"!\$B\$3:\$O\$3",0),0),0)
0

Author Comment

ID: 39276163
All my worksheets are named DD-MMM-YYYY and in cell A2 I have a date.  I had to add parentheses to the formula however I keep getting a Ref error any ideas?=VLOOKUP(E\$3,INDIRECT(TEXT(A2,"dd-mmm-yyyy")&"!\$B\$31:\$O\$78"),MATCH(\$B4,INDIRECT(TEXT(A2,"dd-mmm-yyyy")&"!\$B\$3:\$O\$3"),0),0)
0

LVL 12

Accepted Solution

telyni19 earned 500 total points
ID: 39276175
Try adding single quotes around the sheet names, then:
=VLOOKUP(E\$3,INDIRECT("'"&TEXT(A2,"dd-mmm-yyyy")&"'!\$B\$31:\$O\$78"),MATCH(\$B4,INDIRECT("'"&TEXT(A2,"dd-mmm-yyyy")&"'!\$B\$3:\$O\$3"),0),0)
0

LVL 35

Expert Comment

ID: 39276183
=VLOOKUP(E\$3,INDIRECT(TEXT(A2,"dd-mmm-yyyy")&"!\$B\$31:\$O\$78"),MATCH(\$B4,INDIRECT(TEXT(A2,"dd-mmm-yyyy")&"!\$B\$3:\$O\$3"),0),0

Should \$B4 be \$B11 (as per your original question)?
0

## Featured Post

### Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.