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

B

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.
 
J

Jim Thomlinson

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

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.
 
T

Tom Ogilvy

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

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
 

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

Top