VLOOKUP/MACRO referencing data in a report dated 1 day previous.

[Rhinogaz]

Hi All I am trying to write a VLOOKUP formula in a macro run from a template report to Return data from yesterdays report (which started life as the template then saved as that days report). The normal VLOOKUP formula works fine normally when both reports are open. I wanted to automate the process as much as possible which is why I wanted to incorporate the formula in a macro. The date in the file is displayed as "Report 19.12.19.xls". When I run the macro it stops with a compile error: Sub or Function not defined and highlights TODAY in the formula. Can anyone help please?

' ActiveCell.FormulaR1C1 = _
    '   "=VLOOKUP(C12,INDIRECT("[Offline Report_"&TEXT(TODAY()-1,"DD.MM.YY")&".XLS]Offline Tuesday to Friday Repor'!C12:C17,6,FALSE))

 

[Rhinogaz]

You can't VLOOKUP(L:L... - that's what's giving you the #REF error. You look up one value, not en entire column. Something like =VLOOKUP(L1,...

The formula I sent you was directly into the cell (not vba) and I have just typed it in again to take a copy of the formula and it works returning a comment from yesterdays report. On this one I have left in the dollar signs-

=VLOOKUP($L:$L,'[Offline report 30.12.19.xls]Offline Tuesday to Friday Repor'!$L:$Q,5,FALSE)

It does work, I will try with the cell ref: as you suggest and see if that works.

Thanks