best way to link cells from other worksheets/workbooks

Hi,
I'm new to this forum, so I am glad I found you all and hope you can help.


I have a workbook where I record monthly stock data. this data is linked to other worksheets and some other workbooks where I have formula's to do various calculations. The way I link them is using the "=" and putting in the worksheet/cell link. The problem this posses is if I add new column in the stock data sheet, the linked cells don't always adjust.

Attached are two sample workbooks: "test source" and "test summary"

Test source is where I enter the monthly ending balance for each fund. Each brokerage has its own sheet. so Ameritrade and PNC are two separate sheets. I have a few funds with each brokerage.
Test summary aggregates the monthly data and does totals, etc.

How do I keep the data linked? I sometimes add funds in between existing funds by inserting columns, this often messes up the links.

I assume there is a better way of connecting the Test summary data to the raw data in test source.

thanks,
Rick

Thanks, I will try the index and match solution. having the sheets opened when adding columns didn't always work. I think because the workbooks are saved on a network drive and there are more than one path to get to, so it just didn't reliably work.

Rick