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

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

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

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 -