2

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.

Improve this question

1 Answer 1

Reset to default
0

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)
Improve this answer
8
  • I'm not 100% sure what you're intending here. Referring back to my example, I inserted your formula into the Worksheet B, Column B, and made to reference the value in Column A (which is taken from Worksheet A.) My Worksheet B formulas are now =Sheet1!A1 | =OFFSET(INDEX(Sheet1!$A$1:$A$4,MATCH(A1,Sheet1!$A$1:$A$4,0)),,1) with an output of Apple | Red Could you please give an example of what the output of your formula should resemble?
    – dude17943
    Commented Sep 10, 2016 at 0:38
  • Use the second formula on worksheet B, replacing Sheet2 with worksheet A
    – Raystafarian
    Commented Sep 10, 2016 at 0:40
  • Right, which makes my Worksheet B formulas =Sheet1!A1 | =OFFSET(INDEX(Sheet1!$A$1:$A$4,MATCH(A1,Sheet1!$A$1:$A$4,0))‌​,,1) with an output of Apple | Red
    – dude17943
    Commented Sep 10, 2016 at 0:49
  • You would get that, but if you sort anything on sheet1 it will remain correct on sheet B. If you change the "apple" to "green apple" it won't know that.
    – Raystafarian
    Commented Sep 10, 2016 at 0:51
  • It actually does, though, since the cell address in MATCH links it to the data in the first sheet, but whatever. This doesn't really address the primary problem of the data in Worksheet 2, Column 2 mixing the rows when data in the first sheet is sorted.
    – dude17943
    Commented Sep 10, 2016 at 0:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .