How to refresh data from external source

[colinheslop1984]

I currently have a link on a spreadsheet that will open the source file for when data may need refreshing.

How do I get the data to refresh by clicking this link without the source file opening up?

 

[xenou]

Hi, can you give some more information about what kind of a link you are working with and what is the type of data that is being refreshed? I'm not sure what you mean by "have a link on a spreadsheet" - how does that work? What is it doing?
Regards,
xenou.

 

[colinheslop1984]

I have sumproduct formula that returns data from an another workbook.

The link I have at the moment is just a link to that workbook, so when its clicked that document opens up which intern updates any calculations based on new criteria.

Criteria cells are a "from" and "to" number sequence using a validation list.

What I am hoping to do is pull any required data to be able to satisfy calculation without the source file needing to be open.

 

[sandy666]

maybe use Power Query, source files don't need to be open

 

[xenou]

It is my understanding that linked files don't need to be opened for links to be updated. So I'm not sure why you are saying that's not possible. If you are saying the intern needs to update the target workbook (the one you are linking to) well in that case you do need to open the workbook to makes changes in it.

 

[colinheslop1984]

I might just be making this harder than it needs to be but here is what I am working with.

I have workbook FY18-19 and workbook FY19-20

FY19-20 has a table that pulls data from FY18-19 based on 3 criteria. When one of the criteria is changed on FY19-20 I want FY18-19 to change with it and run its new calculation in the background without requiring it to be open.

Is this possible?

I don't think power query isn't fit for purpose for this one but might look in to it

 

[xenou]

I see. I don't think it's possible. You can pull data from a closed workbook, but a closed workbook cannot recalculate.

 

[colinheslop1984]

No problem, thanks for your help, I will rethink the way the layout of my workbook or use some helper cells