Help! VLOOKUP / INDIRECT formula when the data is a formula

[jennyclark]

Hi,

Please note I am self taught in Excel. I will try best explain the best I can, but don't know the lingo

I am doing a formula (below) which looks up data in a sheet name. The data is a date for example 01.02.22

=VLOOKUP($F4,INDIRECT("'"&AN$2&"'!"&"F4:W300"),18,0)

Cell AN2 is a formula =C2+7

When AN2 is a formula the VLOOKUP formula doesn't work. But when I type the date straight into AN2 it works.

How do I change it so it does work with the formula?

Thanks in advance,

Jenny

[AliGW]

Administrative Note:

Members will tailor the solutions they offer to the version (NOT release number) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

[AliGW]

Try this:

=VLOOKUP($F4,INDIRECT("'"&TEXT(AN$2,"dd.mm.yyyy")&"'!"&"F4:W300"),18,0)

Failing that, attach a workbook.

[jennyclark]

Thank you

[AliGW]

Glad to have helped.

If you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.