best way to link cells from other worksheets/workbooks

[r9thomas]

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.

what i have is have a source worksheet where the columns are funds and the rows are months. Each month I input the ending balance of each fund.

I then have other worksheets & workbooks where i want that data to show up.

For example. Source worksheet A2 is for a Ameritrade fund "ABC" and its 1/31/18 balance is $100.
In a separate workbook named "Ameritrade Funds", I have a sheet called "ABC". I want the 1/31/18 balance to populate a cell in this sheet so calculations, etc can be viewed in detail for this fund.

Since I add new funds from time to time, I insert columns on the Source workbook (to keep say, all the Ameritrade funds together). If I have them linked, that can mess it up.

How do I keep the data linked?


thanks,
Rick

 

[Jerry Sullivan]

Hi Rick and Welcome to MrExcel,

That's a broad topic, and there isn't necessarily a "right" way to organize and relate your data. Generally speaking, there are benefits to separating your dataset (or database) from the reports that you create to summarize, or analyze your data. This usually involves having one or more tables of records that are only used to store data. The data table(s) should be organized in a way that allows your reports to pull data without reliance on the rows of data being in a specific order, or the fields in the table being in specific columns in your worksheets.

While the data table(s) and reports should be "separate", that doesn't mean they have to be in separate workbooks or even separate worksheets. They should just be distinct parts. Typically, the reports are referencing the data table(s), but the data tables should not be referencing or dependent upon the reports.

I hope this helps a bit. Just ask if you have some specific questions that this broad overview didn't address.

 

[r9thomas]

Thanks Jerry,
I have sample workbooks, but there doesn't appear to be a way to share them. Is there?

But, yes, I use the "source" workbook as a data table for all the funds, grouped by brokerage. This way it's easy to use the statements to enter the ending balance. However, Let's say I five funds with Ameritrade (represented in columns A to E) and five with JpMorgan funds (from F to J). Then I buy another Ameritrade fund and want to insert it after "E", so they all stay grouped. I want to make sure that the workbooks pulling the data from the funds don't get mixed up. In this example, the data for JPMorgan that was in F, is now in G, and the JPMorgan workbook needs to follow that change.
When I linked them before, it didn't always work.

I have been playing around with the Index-Match function, but it hasn't worked when I insert a column. but I don't understand that function fully and maybe there is a way to making that work...or another way.

Thanks,
Rick

 

[Jerry Sullivan]

Rick,

This forum doesn't support attachments, but you can post images, using the tools described here:
https://www.mrexcel.com/forum/about-board/508133-attachments.html

If I'm understanding the layout of your current dataset, Index-Match or Vlookup with Match for the column number should work. You should have a row of headers that have unique identifiers for each fund, such as their tickers.

 

[r9thomas]

Hi Jerry,
I was unable to figure out how to post images.

Here is a simplified version of what I am trying to do:

I have two workbooks: "Source" and "Fund"
Source is a workbook where I input all the various fund balances from various brokerages (see more detailed explanation in my last post)
Fund is the workbook for a particular fund where I want the data from the source to populate.

Source is set up like this:
Column A has end of month dates (i.e. 1/31/18)
B1 has a unique fund name
Starting with B2, I enter the end of month balance of each fund

Fund workbook is similarly set up:
Column A has end of month dates (i.e. 1/31/18)
B1 has a unique fund name
Starting with B2, I have the Index-Match function entered: =INDEX('[source.xlsx]Sheet1'!$B$2:$B$20,MATCH(A2,'[source.xlsx]Sheet1'!$A$2:$A$20,0))

This works fine. If I enter $500 in B2 of the source workbook, B2 of the Fund shows $500.

Problem:
In the source workbook, if I insert a new column after A, the data that was in B2, is now in C2. The fund workbook formula doesn't change, so it is pulling the wrong data. that is, it is pulling it still from B2, which has data from a new and different fund.

How can I just have it look for the fund name, regardless of where it is in the source workbook.

Thanks!
Rick

 

[Jerry Sullivan]

For Index-Match, you could use:
=INDEX([source.xlsx]Sheet1!$A:$AA,MATCH($A2,[source.xlsx]Sheet1!$A:$A,0),MATCH(B$1,[source.xlsx]Sheet1!$1:$1,0))

For Vlookup-Match, you could use:
=VLOOKUP($A2,[source.xlsx]Sheet1!$A:$AA,MATCH(B$1,[source.xlsx]Sheet1!$1:$1,0))

In both cases, you are using MATCH to find the fund's column in the Source workbook.