Referencing a sheet by its order or index number in a Vlookup formula

[Wheelie_Awesome]

I would like to have a macro with vlookups for a workbook that I add weekly sheets to by date. So Sheet1 is current date, Sheet2 is last weeks date. Then next week I'll add another new Sheet1, and so on. I want my vlookup to also look in Sheet2, rather than the name of the sheet.

I've been messing around by making a new module with:
Function SHEETNAME(number As Long) As String
SHEETNAME = Sheets(number).Name
End Function

And can get a correctly returned result for summing up a column. =SUM(INDIRECT("'"&SHEETNAME(2) &"'!S:S"))
But I can't figure out how to get the Vlookup to work. Is this even possible?

My regular vlookup formula would look like this: =VLOOKUP($C3,'8-11-2021'!$C:$G,2,0)
Where 8-11-2021 is the sheet name that I want to reference just as Sheet2.

 

[FormR]

Hi, welcome to the forum!

You could try like this.

=VLOOKUP($C3,INDIRECT("'"&SHEETNAME(2)&"'!C:G"),2,0)

 

[Wheelie_Awesome]

That did it! Thank you so much.

 

[Adrianz1001]

That did it! Thank you so much.

Hi, I'm happy you got it working! I'm running into the same exact issue with one of my workbooks, but unfortunately I was not able to get it working. I'm trying to put the vlookup on cell L3 of sheet 1 and pull info from sheet 2 range F through M. I've pasted my module below, how can I get this working?

Function SHEETNAME(number As Long) As String
SHEETNAME = Sheets(number).Name
End Function

Sub VLookUp()
    Range("L3").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP($F3,INDIRECT(" '"&SHEETNAME(2)&"'!F:M"),7,0)"
    Range("L3").Select
    Selection.AutoFill Destination:=Range("L3:L1000")
    Range("A1").Select
End Sub

 

[FormR]

how can I get this working?

Hi, welcome to the forum!

You could try like this (note how I've used a different name for the macro - you should really avoid using names that already have a meaning in Excel or VBA).

Sub myVLookUp()
Range("L3:L1000").Formula = "=VLOOKUP($F3,INDIRECT(""'""&SHEETNAME(2)&""'!F:M""),7,0)"
End Sub

Or as you are applying the formula via code, you could possibly dispense with the indirect function and UDF altogether and just use.

Sub myVLookUp()
 Range("L3:L1000").Formula = "=VLOOKUP($F3,'" & Sheets(2).Name & "'!F:M,7,0)"
End Sub

 

[Adrianz1001]

Hi, welcome to the forum!

Wow, you make it look easy! Thanks so much for getting this to work.