Sorting to alternate worksheets from master worksheet

  • Thread starter Thread starter DataDan
  • Start date Start date
D

DataDan

This is a learning project in the works.

I have created a master worksheet with 5 columns of data that I pos
additional data to the master list each week involving all 5 columns.
want to create additional worksheets that will update from the maste
worksheet automatically but my main concern at this point is how to d
an automatic sort from the master list when I click on each additiona
worksheet. For instance the master worksheet is sorted by column A a
the priority, alternate worksheet 1 is sorted by column B as th
priority so on and so forth. Basically I want to interpret the maste
data differently in each worksheet.

Is their an automatic function that will do this or is it a custo
script?

Additionally is their a way to upgrade automatically to each additiona
alternate worksheet when the data is posted to the master worksheet?

Ultimately I will work on creating a basic script to ask for specifi
inputs for each column that will be posted to the master worksheet.

I know that this is a lot going on but if someone can get me going i
the right direct I would be most grateful.

Thank yo
 
One way would be to use lookup functions,
for e.g. VLOOKUP's or OFFSET(..MATCH()...)
in the dependent sheets (i.e. your worksheets2, 3, etc)
instead of just simple links to cells in the "master" sheet (your
worksheet1, say)

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()...)?:

Assume 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