Populating a cell with a formula that references a previous sheet in that workbook

[fatesdefiance]

I am using some VBA in a source workbook to create a new sheet in a Output workbook and copy/paste various information into that new sheet.
Each time that my VBA is run, it will create another new sheet in that Output workbook.

Here is the problem:
A couple of the columns in my newly created sheet need to run vlookups and such on data in the previous sheet in the Output workbook.

So, for example, if my VBA has been run a couple times before, and there now exist Sheet1, Sheet2, and Sheet3 in the Output workbook:
I run the VBA again, and a new sheet, using the default name Sheet4, is created. I want to populate a formula into a column in Sheet4 that will vlookup info from Sheet3. When the VBA is run again later, creating Sheet5, there will be a column in there that executes a vlookup against Sheet4...and so on.

Unfortunately, I cannot figure out any way to either have a formula directly reference the immediately preceding worksheet in its workbook (without knowing the name of the sheet), or to have VBA obtain the name of the immediately preceding sheet and use that to populate a vlookup formula in my new sheet.

Any ideas? More info needed?

Any help would be appreciated.

[CAntosh]

I use a popular UDF called SHEETOFFSET:

Please Login or Register  to view this content.

=SHEETOFFSET(-1,B3) will return the value in B3 on the previous sheet, regardless of that sheet's name. Similarly, =SHEETOFFSET(2,B5) will return B5 from two sheets forward.