XL2002 - VLOOKUP with variable Sheet Name
 

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

XL2002 - VLOOKUP with variable Sheet Name
 

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

On Jul 29, 5:10*pm, Trevor Williams
wrote:
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

XL2002 - VLOOKUP with variable Sheet Name
 

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


"Pete_UK" wrote:

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

On Jul 29, 5:10 pm, Trevor Williams
wrote:
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

XL2002 - VLOOKUP with variable Sheet Name
 

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

Pete

On Jul 29, 5:26*pm, Trevor Williams
wrote:
Blimey - that's gotta be the quickest response I've had on a forum!
Thanks Pete, I'll give it a go.



"Pete_UK" wrote:
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

On Jul 29, 5:10 pm, Trevor Williams
wrote:
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- Hide quoted text -

- Show quoted text -