XL2002 - VLOOKUP with variable Sheet Name

[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'!A110,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

 

[Pete_UK]

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

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

Hope this helps.

Pete

 

[Trevor Williams]

Blimey - that's gotta be the quickest response I've had on a forum!
Thanks Pete, I'll give it a go.

 

[Pete_UK]

You're welcome, Trevor - I just happened to be browsing when your post
came through.

Pete