Keeping data together

  • Thread starter Thread starter Compass
  • Start date Start date
C

Compass

On worksheet1, I list identifying information about my
clients in columns 1, 2, etc.. On worksheet2, et.al., I
pasted a link to those columns, so that the data I enter
on worksheet1 will be mirrored exactly on the subsequent
worksheets.

My goal is to have separate worksheets with different
kinds of data that I will keep about the clients whose
identifying information I will list and manage on
worksheet1.

When I sort the data on worksheet1, the linked cells in
the linked worksheets mirror that sort, BUT, the cells
next to those linked column cells are not sorting
respective to the linked cells on those linked worksheets.

For example, the sort on worksheet1 results in Adams's
phone number being next to Zanks' phone number and vice-
versa on worksheet2.

Why is this happening?
 
Think you'll need to use lookup functions,
for e.g. VLOOKUP's or OFFSET(..MATCH()...)
in the dependent sheets (your worksheets2, et. al.) rather than just
simple links to cells in the "master" sheet (your worksheet1)

This arrangement will work if you have a key column that is shared between
the dependent sheets and the "master" sheet with *no* duplicates in that
column. If necessary, this key column could also be a "concatenated field"
column
(e.g. in C2: = A2&"_"&B2) which uniquely identifies each client

Use "False" or "0" as the range_lookup in VLOOKUP /
as the match_type in MATCH() for an exact match

Let's take an example set-up, using OFFSET(..MATCH()...):

In Sheet1 (Master) in A1:C4, you have
-----------------------------------
Client Detail1 Detail2
1234 figs1 text1
2345 figs2 text2
1235 figs3 text3

where the key col is col A, with no duplicates

In Sheet2 (dependent sheet) in A1:C1, the col headers are:
-------------------------------------------------------------------
Client Detail1 Detail2

And you have in A2: =Sheet1!A2
which is copied down col A

Put in B2:
=OFFSET(Sheet1!$A$1,MATCH($A2,Sheet1!$A:$A,0)-1,COLUMN()-1)

Copy B2 across to C2, then down to the last row of data
 
Back
Top