Solved

Referencing a sheet via cell

Posted on 2013-06-25
4
257 Views
Last Modified: 2013-06-25
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
Comment
Question by:Jenedge73
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 12

Expert Comment

by:telyni19
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

by:Jenedge73
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

by:
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

by:[ fanpages ]
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question