Excel Dashboard design under the hood

What I need to do is create a simple dashboard (Spreadsheet_Destination) that graphs the most recent 20 values of a list in Spreadsheet_Source which is located elsewhere on the company shared network. I thought this would be easy. I made a list in Spreadsheet_Source and a graph of it, then copy and pasted that graph on to Spreadsheet_Destination. Every time I typed in Spreadsheet_Source I saw the immediate update on the Spreadsheet_Destination graph which of course was showing at the time. Thought all was good until I tried opening the Spreadsheet_Destination in another folder on the company network just to prove that the links worked, and that adding a value in Spreadsheet_Source would immediately be reflected on Spreadsheet_Destination. Since the source spreadsheet never moved the links were intact. The experts here know what I am going to ask. How to have the destination spreadsheet acting as a dashboard update in conjunction with the source spreadsheet.

Any help would be greatly appreciated as I have spent way too long digging around the internet for just the perfect solution to this particular problem. Please be explicit.

Thanks.