Storing range in a cell and using it with Vlookup

[Malick2020]

I need to do vlookup for a range which is variable depending on the date

for example: =Vlookup(a2,http://mypc/[attendance.xlsx]7th March'!$AR:$AS,2,0)

so for the link of the sheet is fine no need to change, but the tab in the sheet it changes according to date:

for example:

if the date is 8/3/2022 the formula will be like this: =Vlookup(a2,http://mypc/[attendance.xlsx]8th March'!$AR:$AS,2,0)

i tried to put the value of the date in a cell and using another vlookup inside the date but it didnt work. i also tried to use indirect formula to recall the sheet link but it didnt work also showing #Ref error.

Btw the lookup value is coming from a table cell.

so any ideas please?

 

[GlennUK]

You cannot link via INDIRECT to a closed workbook. If the workbook is open, then fine. Just build the link in INDIRECT just using filename and sheet, i.e. without full path.

 

[Malick2020]

You cannot link via INDIRECT to a closed workbook. If the workbook is open, then fine. Just build the link in INDIRECT just using filename and sheet, i.e. without full path.

so the only way to build a link and use it with formula is using INDIRECT? i tried filter and index actually and it worked but building link wasnt success

 

[GlennUK]

Filter and index won't work if you need to point to different sheets in an external workbook. Show your attempt to build a link in Excel using INDIRECT.

 

[Malick2020]

Filter and index won't work if you need to point to different sheets in an external workbook. Show your attempt to build a link in Excel using INDIRECT.

here is an example:

=FILTER('https://MREXCEL.COM/REPORTING[ATTENDANCE.xlsx]11th March '!$AS:$AS

i want "11th March" to change according to another cell which containing the date using vlookup from another reference sheet.

 

[GlennUK]

That is not attempt to build a link in Excel using INDIRECT. It also shows a fully qualified external link, indicating that you do NOT have the external workbook open in the same Excel session. Look at this and see if you understand : =Vlookup(a2,INDIRECT("'[attendance.xlsx]" & cell ref containing 8th March as text & "'!$AR:$AS"),2,0)