0

I am a building a workbook (spreadsheet) that has worksheets labeled by month; e.g., "Jan-12", "Dec-12", and so on. I need VLOOKUP to be able to change where it looks based on the name of any given worksheet without having to change the formula every time. I found this answer and it works great for the VLOOKUPs that are in different workbooks which I have also, but I can't get it to work in the same workbook.

=VLOOKUP(A1, INDIRECT(CONCATENATE("'[Datafile.xlsx]",B1,"'!$A1:F500")), 3, False)
Improve this question

1 Answer 1

Reset to default
1

First of all,

CONCATENATE(thing1, thing2, thing3)

can be abbreviated

                      thing1 & thing2 & thing3

They mean the same, but the second form (obviously) takes less room, so some people might find it easier to read.

Your question seems to be a little vague –– you didn’t say when you want to reference the same workbook, and when you want access a different one.  So fill in the blank.  And since you seem to have the VLOOKUP part working, the question comes down to the referencing part.  In other words, how to adapt

INDIRECT(CONCATENATE("'[Datafile.xlsx]", B1, "'!$A1:F500"))

which we can abbreviate to

INDIRECT("'[Datafile.xlsx]" & B1 & "'!$A1:F500")

So try

INDIRECT("'" & IF(condition, "", "[Datafile.xlsx]") & B1 & "'!$A1:F500")

where condition is the condition under which you want to use the current file.  In words, concatenate a single quote ('), conditionally the workbook name (in brackets) or not, then the sheet name (I assume that’s what’s in B1), a second quote, an exclamation mark, and the cell range.

Improve this answer
1
  • 1
    My guess is that the OP only wants to do this in the current workbook now, so is deleting "'[Datafile.xlsx]" from the CONCATENATE and therefore losing the initial single quote ' and ending up with a reference that the INDIRECT can't handle.
    – AdamV
    Commented Jan 12, 2013 at 16:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .