Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 12th 05, 01:06 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2005
Posts: 7
Default Bringing in information from one worksheet to another dependent on the 2nd

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.


  #2   Report Post  
Old May 12th 05, 01:19 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2004
Posts: 983
Default Bringing in information from one worksheet to another dependent on

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" wrote:

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.


  #3   Report Post  
Old May 12th 05, 01:24 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2005
Posts: 7
Default Bringing in information from one worksheet to another dependent on

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.

  #4   Report Post  
Old May 12th 05, 06:48 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 27,285
Default Bringing in information from one worksheet to another dependent on

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.

--
Regards,
Tom Ogilvy


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



  #5   Report Post  
Old May 12th 05, 06:53 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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.





  #6   Report Post  
Old May 12th 05, 10:01 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2005
Posts: 7
Default Bringing in information from one worksheet to another dependent on the 2nd

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
bringing data together from two worksheet for comparison jb Excel Worksheet Functions 1 September 21st 07 02:28 PM
Bringing conditional data from another Worksheet Nikki Excel Worksheet Functions 2 March 31st 07 06:44 AM
bringing data in from another worksheet stacy05 Excel Discussion (Misc queries) 1 February 23rd 07 07:39 PM
bringing data in from one worksheet to another stacy05 Excel Worksheet Functions 0 February 23rd 07 01:51 PM
Bringing additional information in with combo box selection JD Excel Worksheet Functions 1 February 24th 05 01:45 AM


All times are GMT +1. The time now is 07:41 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017