Sharing data between excel tables in SharePoint

Hi All,

This might have been answered somewhere else but can't find the answer.

I am trying to link data between Excel tables in SharePoint. Basically, I have multiple workbooks where I use a VLOOKUP formula to pull data from the other workbook. 

Problem is it only works when all workbooks are open (e.g. the workbook that has the information in originally). I thought with it being on SharePoint it would pull the information automatically without the need to keep the workbooks open. I even thought it would work on Excel online, but no luck.

Can anyone help?