Bringing in information from one worksheet to another dependent on the 2nd

[B Smith]

What I am trying to do is bring in information from one worksheet to a
corresponding record on another worksheet, all of which are in the same
workbook.
I use Access XP to export two sets of data sorted differently to Excel
XP.

Sheet 1
what I already have on sheet 1
already sorted by SSN# then name
subtotal on Week T based off of name
I then use a formula ("=$D2/SUMIF($B:$B,$B2,$D:$D)") for % of total and
fill down on column E
I use a macro to do the formula to fill until the lastcell (always
works)

A B C D E
1 Group Name SSN# Week T % of Total
2 5 Bill 010101 10 20%
3 5 Bill 010101 40 80%
4 Bill Total 50 100%
5 8 Jane 111111 500 50%
6 8 Jane 111111 250 25%
7 8 Jane 111111 250 25%
8 Jane Total 1000 100%
9 2 Jerry 661234 50 100%
10 2 Jerry Total 50 100%
11 2 Milt 512512 5 5%
12 2 Milt 512512 65 65%
13 2 Milt 512512 30 30%
14 Milt Total 100 100%


Sheet 2
what I already have on sheet 2
sorted by group with subtotal thing totaling Week T based off of Group

A B C D
1 Group Name SSN# Week T
2 2 Milt 512512 5
3 2 Milt 512512 65
4 2 Milt 512512 30
5 2 Jerry 661234 50
6 2 Total 150
7 5 Bill 010101 10
8 5 Bill 010101 40
9 5 Total 50
10 8 Jane 111111 500
11 8 Jane 111111 250
12 8 Jane 111111 250
13 8 Total 1000


I need sheet 2 to refer back to sheet 1 to get the "% of total" for
each corresponding entry

A B C D E
1 Group Name SSN# Week T % of Total
2 2 Milt 512512 5 5%
3 2 Milt 512512 65 65%
4 2 Milt 512512 30 30%
5 2 Jerry 661234 50 100%
6 2 Total 150
7 5 Bill 010101 10 20%
8 5 Bill 010101 40 80%
9 5 Total 50
10 8 Jane 111111 500 50%
11 8 Jane 111111 250 25%
12 8 Jane 111111 250 25%
13 8 Total 1000


I have thought about using a if...then statement in a VB macro, but the
number of names and which names in the worksheet change. I believe I've
looked at any relative page on McRitchie's site (on
http://www.mvps.org/dmcritchie/excel/excel.htm ), and many others as
well. I am still new to Excel, macros, and VB, so I thank you all in
advance for your time.

 

[Guest]

What you are doing is most easily done with a pivot table. Try creating a
pivot from your source data and you should be off to the races without any
code whatsoever.

HTH

 

[B Smith]

Ok, I've made an acceptable pivot table that I can use to transfer the
% of total to sheet 2. I tried making a pivot table with all the info,
but it does not work very well with all of the data going in the row
section (if I put Week T in the data section, it adds up the Week T
(which is just 1 item) for each group, name, and SSN#, and then puts
another unneeded subtotal there, resulting in unneeded subtotals under
name, SSN#, and Week T). If I am coming at this approach correctly, how
can I make sheet 2 correctly get the % of total from sheet 1's pivot
table? (yes, I know some code is probably required, but I'll manage.)
Thanks for the help.

 

[Tom Ogilvy]

Look at http://www.contextures.com/tiptech.html

Debra Dalgleish's site. She has a lot of information on using and
programming pivot tables.

Once you get it going manually, you can use the macro recorder to get
insights into how to make adjustments with code.

 

[Tom Ogilvy]

It looks like you have the same numbers on both sheets, why can't you use
the same formula?

 

[B Smith]

Thanks Jim and Tom! Tom, I think I will be able to get it to work after
looking at that website- I am going to go ahead and learn all I can
about pivot tables amd work from there. Both sheets use the same
numbers, but the 2nd is sorted differently with subtotals based off of
a different column. I use my % of Total for that person's particular
total, so in sheet 2 the formula does not give the same values as the
first since the total formula would give that particular group's total.
Thanks again for all the info!

Brett