Can I use column() in the sheet name inside a vlookup?

[Scotbot]

Hi, I have this formula: =VLOOKUP($A2,'[Worked Data.xlsx]1'!$D$2:$D$65000,1,FALSE)

Which references a workbook called 'Worked data.xlsx' and this has a series of worksheets '1' '2' '3' etc.

I would like the formula to increment the sheet name by 1 each time it is copied/filled one column to the right.

I've tried using column() to return a number that varies by column, which I have used in the past to increment the column number that is being referenced, but it didn't work when referencing a sheet name (or I didn't use the correct syntax).

I guess I could create forumula to create a text string using the fixed string "[Worked data.xlsx]" and the column() function and then insert this into the vlookup, but I have no idea how is should be written, or if it's possible.

Any suggestions?

Many thanks!

[Søren Larsen]

You can solve it by using =Indirect().

[Scotbot]

Thanks,

I used

=VLOOKUP($A2,INDIRECT("'[Worked Data.xlsx]"&(COLUMN()-5)&"'!$D$2:$D$65000"),1,FALSE)

Which has worked - the formula is in column F.

Scott.

[Søren Larsen]

You are very welcome.