How do I duplicate column data and preserve row relationships across worksheets?

Question

I have a large amount of data on one worksheet, and would like to split it between two separate linked worksheets.

Assume I have these columns on my first worksheet (Worksheet A):

Apple   | Red
Banana  | Yellow
Lime    | Green

I would like to connect that first column to a column on my second worksheet (Worksheet B), so that I can enter data applying to the member data in the first column.

Apple [Linked]    | $.25
Banana [Linked]   | $1.00
Lime  [Linked]    | $1.50

Simply linking the cells using a "=#cellAddress" formula or Paste Special > Paste Link does not fulfill my needs; if either of the first columns are sorted, the data in the second column on the opposing worksheet does not move in kind.

For example, if you attempt to sort the first column of Worksheet A alphabetically....

Lime    | Green
Banana  | Yellow
Apple   | Red

, Worksheet B will still have...

Lime  [Linked]    | $.25
Banana [Linked]   | $1.00
Apple [Linked]    | $1.50

The data I am using is also added to and changes regularly, so simply copying and pasting the column from Worksheet A to Worksheet B does not fulfill my needs either; the data needs to update in real time.

I've tried both formulas and setting up relationship tables, but nothing lets me keep my row data straight across the worksheets.

I've tried Table Relationships, simply copying the values from the Worksheet A headers and typing in my Worksheet B data, then creating a table for both and setting up a relationship. This does not allow for updating the column data, though- attempting to add a "Pears" row or renaming "Apple" to "Green Apple" does not propagate from Worksheet A to Worksheet B.

I need some way of sharing this column data across both Worksheets while still preserving the independence of each worksheet's row data (through tables, probably).

I've been working on this for a few days and I'm not sure what else I can even try at this point.

Answer 1

You'll need to look up the value after it sorts with something like:

=INDIRECT(ADDRESS(MATCH("Apple",A:A,0),1))

I wouldn't use Indirect though, this is better:

=INDEX(Sheet2!$A$1:$A$4,MATCH("Apple",Sheet2!$A$1:$A$4,0))

To get cells in reference to that cell, you will need:

=OFFSET(INDEX(Sheet2!$A$1:$A$4,MATCH("Apple",Sheet2!$A$1:$A$4,0)),,1)