View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Tom Ogilvy Tom Ogilvy is offline
external usenet poster
 
Posts: 27,285
Default Bringing in information from one worksheet to another dependent on the 2nd

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

--
Regards,
Tom Ogilvy


"B Smith" wrote in message
ups.com...
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.