XL2002 - VLOOKUP with variable Sheet Name

  • Thread starter Thread starter Trevor Williams
  • Start date Start date
T

Trevor Williams

Hi All

I am trying to create a formula to look up data on a sheet that has a
variable sheet name. Here's the current formula:

=VLOOKUP(E7,'ac1'!A1:D10,3,FALSE)

What I need to do is change the 'ac1' to another name based on which sheet
the user is looking at. The sheet name could be ac1, ac2, ac3 etc - the
number that appears in the sheet name exists in cell A1 on the sheet that the
formula is in.

I have tried creating the sheet name in cell B1 using ="ac"&A1, and then
trying to reference that in the vlookup formula but it doesn't work.

Any help gratefully received.

Regards
Trevor Williams
 
Suppose your sheet number is in A1, then change your formula to this:

=VLOOKUP(E7,INDIRECT("'ac"&A1&"'!A1:D10"),3,FALSE)

Hope this helps.

Pete
 
Blimey - that's gotta be the quickest response I've had on a forum!
Thanks Pete, I'll give it a go.
 
You're welcome, Trevor - I just happened to be browsing when your post
came through.

Pete
 
Back
Top