Wheelie_Awesome
New Member
- Joined
- Aug 18, 2021
- Messages
- 4
- Office Version
- 365
- 2013
- Platform
- Windows
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.
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.