Help w/ linking data between worksheets

D

dawganavi

I would like to set up my workbook so that: if in a certain column on the
main sheet code #'s 1-7 are entered, the corresponding row is automatically
transfered over to the worksheet related to those #'s....1-7. Any
suggestions?
 
M

Max

Perhaps this approach would work for you ..

In Sheet1
------------
Let's say you have 3 cols of data across in cols A:C,
headers in row1, data in row2 down:

Code Field1 Field2
C1 Data1 Data1
C1 Data2 Data2
C2 Data3 Data3
etc

The identifier column for auto-extract of corresponding rows into the other
"Code" sheets is col A: "Code", where the codes are entered as
*alphanumerics*: C1, C2, ... C7 (instead of plain numbers 1,2,3 .. as
posted. This change is necessary for the extract to work.)

List across in row1 in 7 empty cols to the right,
say in cols D:J, the 7 codes: C1,C2,C3 ... C7 (enter in D1:J1)

Put in D2: =IF(ISBLANK($A2),"",IF($A2=D$1,ROW(),""))
Copy D2 across to J2, then down as many rows as there is data
or copy down to a "safe" max expected number of rows,
say until J100

The above will set it up for us to extract nicely
into each of the 7 "code" sheets

In sheet: Code1
---------------------
With the same headers in row1 across in cols A:C
(Code, Field1, Field2)

Put in A2:
=IF(ISERROR(MATCH(SMALL(Sheet1!$D:$D,ROW(A1)),Sheet1!$D:$D,0)),"",OFFSET(She
et1!$A$1,MATCH(SMALL(Sheet1!$D:$D,ROW(A1)),Sheet1!$D:$D,0)-1,COLUMN(A1)-1))

Copy A2 across to C2, then copy down as many rows as you copied the formula
in cols D to J in Sheet1, say to C100.

In A2:C100 will be the auto-extracted details
of all the 3 cols of info for Code "C1" from Sheet1
--
Repeat the construct above for the rest of
the other Code sheets: Code2, Code3, .. Code7

The only change needed in the formula in A2 for each Code
is to the col ref "$D:$D".

For each Code, just change the col ref "$D:$D"
in the formula to point
to the Code's column set-up in Sheet1
(viz. col D is for Code1, col E for Code2 ... col J for Code7):

Code2: Change $D:$D to $E:$E
Code3: Change $D:$D to $F:$F
and so on

Just copy>paste the formula above for Code1 into A2
in each sheet, then do an Edit > Replace to
change the col refs in the formula

Fill A2 across to C2, then fill down (as done for Code1)
 
D

dawganavi

Incredible amount of help. Thank you. This may be a bit beyond my
comprehension, but I'm going to give it my best shot. To clarify my
situation:

Column E is the critical column. Headers take up 3 rows in my workbook.
Data entry begins with row 4. My hope is that when, for example, the entry
in column E, row 4 is "1" (C1?) that the entirety of row 4 will then be
copied to the corresponding sheet for "1". By entering "1" in the main data
entry sheet, it essencially becomes a trigger that will extract that row of
data to the new sheet.

Before I get too crazy with attempting what you propose, are we on the same
page here? I sincerely appreciate your help. Thank you.
 
D

dawganavi

OK Max. Well, I think I've got this right. When I made my last post I
didn't quite get it, but once I got into the process I think I understood
what you were going for. What I ended up with was:

=IF(ISBLANK($A2),"",IF($A2=D$1,ROW(),"")) in all the data entry fields
on sheet 1 and copied across my 12 data entry colums and down to row 200.
"C1:C7" in sequence in all the fields to the right of the data entry fields,
and copied all the way down. Then on Code 1:

=IF(ISERROR(MATCH(SMALL(Sheet1!$D:$D,ROW(A1)),Sheet1!$D:$D,0)),"",OFFSET(Sheet1!$A$1,MATCH(SMALL(Sheet1!$D:$D,ROW(A1)),Sheet1!$D:$D,0)-1,COLUMN(A1)-1))
copied across my 12 columns and down to row 200

Unfortunately, after finishing when I went to enter data into the fields in
sheet 1 to try it out, nothing transfered over to code1. In retrospect, can
I assume that it's necessary for the different sheets to be named as we've
been discussing? Sheet1, code1, etc? Should I rename my sheet to sheet 1?
And, does it matter that I have more columns of data than the 3 you exampled
in your instructions? I'll keep playing with it. Thanx again for your
help.
 
M

Max

dawganavi said:
Before I get too crazy with attempting what you propose,
are we on the same page here?
Click to expand...

Just a quick short answer here:
Yes, think we're on the same wavelength <g>

Will post a fuller reply to your 2nd response.
 
M

Max

As stated in the other thread,
think we're basically on the same "page" <g>,
but you might not have adapted the example set-up steps
correctly, going by what you described in your post below

Ok, I gather this is more or less your current set-up ..

From your 1st response:
... Column E is the critical column.
Headers take up 3 rows in my workbook.
Data entry begins with row 4.
Click to expand...

From your 2nd response:
...my 12 data entry colums and down to row 200.
Click to expand...

So, I'll take it your table is in *Sheet1*
in cols A to L (12 cols),
data in row4 to row200
with col E as the trigger col, containing
the alphanumerics: C1, C2, ... C7
(We can't use plain numbers, which will throw the
extract formulas off. The alpha "C" used is arbitrary.)

Try out these constructs ..

In Sheet1
-------------
List across in row3 in 7 *empty* cols to the right
of the data cols A to L, say in cols Q:W,
the 7 codes: C1,C2,C3 ... C7 (in Q3:W3)

Put in Q4: =IF(ISBLANK($E4),"",IF($E4=Q$3,ROW(),""))
Copy Q4 across to W4, fill down to W200

In sheet: Code1
---------------------
Rows 1 to 3 are assumed to contain col headers
across in cols A:L which are identical to that in Sheet1

Put in A4:

=IF(ISERROR(MATCH(SMALL(Sheet1!$Q$3:$Q$200,ROW(A1)),Sheet1!$Q$3:$Q$200,0)),"
",OFFSET(Sheet1!$A$3,MATCH(SMALL(Sheet1!$Q$3:$Q$200,ROW(A1)),Sheet1!$Q$3:$Q$
200,0)-1,COLUMN(A1)-1))

Copy A4 across to L4, fill down to L200

In A4:L200 will be the auto-extracted details
of all the 12 cols of info for Code "C1" from Sheet1
--
Repeat the construct above for Code1 for the rest of
the other Code sheets: Code2, Code3, .. Code7

The only change needed in the formula in A4 for each Code
sheet is to the col ref "$Q$3:$Q$200".

For each Code sheet, just change the col ref "$Q$3:$Q$200"
in the formula to point to the Code's column set-up in Sheet1
(viz. col Q is for Code1, col R for Code2 ... col W for Code7):

Code2: Change "$Q$3:$Q$200" to "$R$3:$R$200"
Code3: Change "$Q$3:$Q$200" to "$S$3:$S$200"
and so on

Just copy>paste the formula above for Code1 into A4
in each sheet, then do an Edit > Replace to change
the col refs in the formula

Fill A4 across to L4, then down to L200
(as done for Code1)

--
The data in A4:L200 in each Code's sheet will auto-retrieve the
corresponding rows for the particular Code, according to the inputs/changes
made in the "Code" column in Sheet1
 
M

Max

Think I've covered the most important bits earlier, but
here's just some clarifying responses to your many Q's
(as I've got some idle time to kill right now said:
.. nothing transfered over to code1. ......
And, does it matter that I have more columns of data
than the 3 you exampled in your instructions? ..
Click to expand...

If you follow the *revised* constructs given earlier which is
tailored to your current set-up, all should work nicely now
... In retrospect, can I assume that it's necessary for the
different sheets to be named as we've been discussing?
Sheet1, code1, etc? Should I rename my sheet to sheet 1?
Click to expand...

No need, the suggested formulas to be placed
in sheets: Code1, Code2 etc
do *not* read the sheetnames
 
M

Max

Whoops ! sorry, guess I messed up on this line ..
Should I rename my sheet to sheet 1?
Click to expand...

Yes, your "master" table is presumed to be in *Sheet1*
The suggested extract formulas in sheets: Code1, Code2, etc
*do* make reference to this sheetname

Think it's easier to change your actual sheetname to "Sheet1" first,
put in the suggested extract formulas and get these working fine,
then you can always rename "Sheet1" back to the former name,
and Excel will auto-change the dependent formulas accordingly

The naming of the other sheets: Code1, Code2 are okay,
these names are not read by the suggested formulas
 
Top