How to copy data from a "parent sheet" to another

C

cdcam

I have a massive master sheet that I need to break down into multiple sheets
based on data. Here is what I have:
2: FIRST LAST BIRTHDAY PHONE ADDRESS CLASS
Then 230 rows of info. What I would like to do is take all the class 1,
class 2, class 3, up to class 20 info (First, Last, Birthday, Phone, Address)
to go to it's own worksheet in the same workbook automatically if possible.
I have tried a suggested macro and a non-array formula but it didn't work and
I just got more confused. I tried a macro from Ron de Bruin but all it did
was put me into an open file window. As a new user to Excel 2007 I'm not
exactly sure how to convert the formulas to work with my worksheet. I have
never written a macro, do not understand all the language. Is anyone able to
help me with this challenge? Please!
 
M

M Kan

A really easy way would be to run a pivot table on the data set, which is
actually, really small. You can drag the class ID into both the row and data
fields. Pick something like Count of Class ID, which will give you a pivot
table with Class ID and a count of records associated to each. Double click
on one of the numbers and a new worksheet will open with just the records
associated with that class ID
 
C

cdcam

Here is the direct link:
http://www.freefilehosting.net/download/3k2f5
This is exactly what my sheet looks like minus 230 rows of info. I would
like each class to have it's own master without having to sort, cut, and copy
a million times over. It would also be great if it automatically updated.
So when a new person is entered on the master that info automatically goes to
the class master. You would be a life-saver if you can help me. With all
this data it takes way too much time to do it all manually. There is a total
of 24 classes. thank you so much for looking at it and taking the time to
help me!!!!
 
M

M Kan

I still think a Pivot Table would be the easiest way to do this. It would
take you all of a minute to create the base pivot table, which will update
(you have to refresh it) when new students are added, but you'd have to
create new master class pages, which just involves doubleclicking the count
value.
 
C

cdcam

It is the easiest way upfront but my info is constantly changing and being
added to. So if there is a way to automate it everytime a new name is added
I need to try that first because it saves a lot of time in the long run. I
like the idea of the pivot table though if I can't make a macro or formula
work. Even if I don't use it for this list I have others I am also working
on that the pivot table will work awesome for!

Thank you for your advice! It is greatly appreciated!
 
M

Max

Here's a quick implemented sample to get you going immediately:
http://www.freefilehosting.net/download/3k2ji
AutoCopy By Key Col to Resp Shts.xls

(Do take a moment to press the "Yes" button below)

It follows the same lines as per my response to your earlier thread, re:
"An alternative formulas driven play is also available to serve your needs ..

Take away this sample construct from my archives:
http://www.savefile.com/files/430142
AutoCopy Lines to Resp Sht Non Array.xls
(Full details inside, nicely rendered. Easy to adapt ..)

Data is continuously entered in a master ("parent") sheet, with lines neatly
auto-copied to each individual ("child") sheet based on the values within a
key col. ... "

--------

I'll post the construct write-up further in this thread later
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik
 
M

Max

Here's the construct write-up for the earlier implemented sample ..

One play which automates it using non-array formulas ..

In sheet: x (the "master")
Assume source data is in cols A to F, data in row2 down,
with the key col = col F (Class)

Note that you'd need to change any class names with "/" to say, underscore,
eg: V 11_12 Boys
This is because the "/" is a prohibited character in sheetnames

Put in G2: =IF(F2="","",IF(COUNTIF(F$2:F2,F2)>1,"",ROW()))
Copy down to cover the max expected extent of source data. Leave G1 blank
This col flags unique items (the Classes) with arb row numbers, which will
be read by the formula in H1 across

Put in H1:
=IF(COLUMNS($A:A)>COUNT($G:$G),"",INDEX($F:$F,SMALL($G:$G,COLUMNS($A:A))))
Copy H1 across as far as required to cover the max expected number of unique
Classes
In H1 across will be extracted all the unique Classes neatly bunched to the
left

Put in H2: =IF(OR($F2="",H$1=""),"",IF($F2=H$1,ROW(),""))
Copy H2 across/fill down

Click Insert > Name > Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")))+1,32)
Click OK

The above defines WSN as a name we can use to refer to the sheetname in
formulas.
It will auto-extract the sheetname implicitly. Technique came from a post by
Harlan.

Then, in a new sheet named after one of the Classes, eg: V 9A
With the same col headers pasted into A1:F1

Put in A2:
=IF(ROWS($1:1)>COUNT(OFFSET(x!$G:$G,,MATCH(WSN,x!$H$1:$IV$1,0))),"",
INDEX(x!A:A,MATCH(SMALL(OFFSET(x!$G:$G,,MATCH(WSN,x!$H$1:$IV$1,0)),ROWS($1:1)),OFFSET(x!$G:$G,,MATCH(WSN,x!$H$1:$IV$1,0)),0)))
Copy A2 across to F2, fill down to say F50, to cover the max expected extent
for any Class. Cols A to F will return only the lines for the class: V 9A
from the mastersheet; x, with all lines neatly bunched at the top. Now, just
dress the sheet: V 9A up as desired, then make copies of it, rename each
copy as the other Classes: Pre-Nursery, A 8C, etc and you'd get the
corresponding results for each of those Classes. Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 16,200, Files: 354, Subscribers: 53
xdemechanik
---
 
C

cdcam

One question:
In office 2007 I can't find an insert > name > define
The closest thing I can find is Name a Range which brings up a window that
has name, scope, comment, refers to. Is this what I am looking for? Is this
where I enter in the name WSN?
 
C

cdcam

Entered in the formula to define name for WSN. But when I enter the formula
into the "child" sheet it brings up another window that wants to know which
file to open to update values:x. What do I do next?

cdcam said:
One question:
In office 2007 I can't find an insert > name > define
The closest thing I can find is Name a Range which brings up a window that
has name, scope, comment, refers to. Is this what I am looking for? Is this
where I enter in the name WSN?

Max said:
Here's the construct write-up for the earlier implemented sample ..

One play which automates it using non-array formulas ..

In sheet: x (the "master")
Assume source data is in cols A to F, data in row2 down,
with the key col = col F (Class)

Note that you'd need to change any class names with "/" to say, underscore,
eg: V 11_12 Boys
This is because the "/" is a prohibited character in sheetnames

Put in G2: =IF(F2="","",IF(COUNTIF(F$2:F2,F2)>1,"",ROW()))
Copy down to cover the max expected extent of source data. Leave G1 blank
This col flags unique items (the Classes) with arb row numbers, which will
be read by the formula in H1 across

Put in H1:
=IF(COLUMNS($A:A)>COUNT($G:$G),"",INDEX($F:$F,SMALL($G:$G,COLUMNS($A:A))))
Copy H1 across as far as required to cover the max expected number of unique
Classes
In H1 across will be extracted all the unique Classes neatly bunched to the
left

Put in H2: =IF(OR($F2="",H$1=""),"",IF($F2=H$1,ROW(),""))
Copy H2 across/fill down

Click Insert > Name > Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")))+1,32)
Click OK

The above defines WSN as a name we can use to refer to the sheetname in
formulas.
It will auto-extract the sheetname implicitly. Technique came from a post by
Harlan.

Then, in a new sheet named after one of the Classes, eg: V 9A
With the same col headers pasted into A1:F1

Put in A2:
=IF(ROWS($1:1)>COUNT(OFFSET(x!$G:$G,,MATCH(WSN,x!$H$1:$IV$1,0))),"",
INDEX(x!A:A,MATCH(SMALL(OFFSET(x!$G:$G,,MATCH(WSN,x!$H$1:$IV$1,0)),ROWS($1:1)),OFFSET(x!$G:$G,,MATCH(WSN,x!$H$1:$IV$1,0)),0)))
Copy A2 across to F2, fill down to say F50, to cover the max expected extent
for any Class. Cols A to F will return only the lines for the class: V 9A
from the mastersheet; x, with all lines neatly bunched at the top. Now, just
dress the sheet: V 9A up as desired, then make copies of it, rename each
copy as the other Classes: Pre-Nursery, A 8C, etc and you'd get the
corresponding results for each of those Classes. Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 16,200, Files: 354, Subscribers: 53
xdemechanik
Click to expand...
Click to expand...
 
M

Max

In office 2007 I can't find an insert > name > define

I don't know what are the steps in xl2007 (I'm using xl2003)
Perhaps someone else out there can help out here?
.. But when I enter the formula into the "child" sheet
it brings up another window that wants to know which
file to open to update values: x.
Click to expand...

The above happens because you don't have a sheet named: x in your file

Easiest way to adapt is to rename your actual mastersheet as: x first.
Then only plug in (ie direct copy n paste from posting or the sample's
formula bar to your formula bar) the point cell formulas as-is, get
everything going first. Then only rename your actual mastersheet to whatever
name, and leave it to Excel to auto-change all dependent formulas.

Another way (easier?) is simply to clear the source data cols A to F in the
sample's sheet: x (To clear means to select the entire cols A to F, then
press Delete key). Then just do a copy n paste special as values/formats of
your actual master sheet's source data into cols A to F. Then extend the
formulas area to the right to suit the actual data extents if necessary.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik
---
 
C

cdcam

I found the insert>name>define
I changed my master to x and the first sheet created shows the data but when
I copy and paste to create the next sheet using paste special all and nothing
happens. I get the formula copied but no data.
The same thing happens when I copied my master into sample. I got the first
two sheets to copy ok but then the following sheets formula but no data.
Thanks!
 
M

Max

.. 7 of 20 sheets did not insert info for some reason.

Check that the actual sheetnames propagated are consistent (spaces, extra
spaces, typos, etc) with the uniques list extracted in H1 across in sheet:
x. This is the usual root cause. The actual sheetnames need to match exactly
(except for case) with the uniques list.

And to easily copy the child sheets, you could just hold down the CTRL key,
then left click on the sheet (you'll see a little icon "sheet" appear), then
drag to either the left or right, release the mouse click, then release the
CTRL key. You could also right-click on the sheet tab > Move or Copy > Check
"Create a Copy" > OK
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 16,200, Files: 354, Subscribers: 53
xdemechanik
 
M

Max

One other thought:
Check also for invalid sheetnames/prohibited characters in your Classes
In my xl2003, sheetnames have to be <= 31 characters, and prohibited
characters include: \ / ? * [ or ]
Amend as required in your Classes col in sheet: x
(similar to what was done for the prohibited character: / in the Class: V
11_12 Boys)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 16,200, Files: 354, Subscribers: 53
xdemechanik
---
 
M

Max

Line
Check that the actual sheetnames propagated ...
Click to expand...

should read as:
Check that the actual sheetnames (when you rename the "child" copies)
Line
.. then left click on the sheet (you'll see a little icon "sheet" appear),
Click to expand...

should read as:
.. then left click on the sheet tab (you'll see a little icon "sheet"
appear),
Click to expand...
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 16,200, Files: 354, Subscribers: 53
xdemechanik
---
 
C

cdcam

AWESOME!!!! It works just as I imagined it could!!! Thank you so much! You
have saved me hours of copying and pasting! Plus I learned a lot more about
formulas and how to write them!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Extract name, address and ph# 8
Grouping Names in Mail Merge 0
Creating an attendance sheet EXCEL 0
Imbedded Data Input sheet 3
Macro to Compile Data from Different Sheets 2
Macro to Combine Data from Multiple Sheets with Cell References 4
Shifting rows to sheet2 using Macro 5
Copy data from one worksheet to another 1

Top