Cannot work out how to get data from a different workbook & tabs using indirect & vlookup

[AustSportingPix]

I posted this on reddit, but no one has been able to help, so trying here.

Used to be a member years ago

This is the current formula I'm using, however I want it to change based on which race track theyre racing at.

=VLOOKUP([@Horse],[Race_tracks.xlsx]Sandown!$A:$G,2,0)



My plan is cell D4 will have what race track they're racing at, currently the template is set up to Sandown.

The formula works well being up the result for just Sandown, but as they race at Caulfield, Felmington, Moonee Valley and so on I will need it to change each day.

I've watched a couple of youtube videos, they seem to explain how to get it working across multiple tabs, but I need it working across tabs within another workbook.

Can it be solved?

 

[jasonb75]

It can be done, but using INDIRECT the source workbook (Race_tracks.xlsx) must be open for the formula to work.

Where A2 contains the workbook name, e.g. Race_tracks.xlsx and B2 contains the sheet name, e.g. Sandown

=VLOOKUP([@Horse],INDIRECT("'["&A2&"]"&B2&"'!A:B"),2,0)