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