#1   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 13
Default Transfer information

I have a spreadsheet with three sheets. One sheet is the main the data sheet
that contains all the information. On the other two sheets I am trying to
setup something that would allow me to pull over data such as name, award
amount, award type, etc. based on the month awarded. I am not sure what would
allow me to do this. Any direction?
  #2   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 268
Default Transfer information

Sounds like another job for Pivot Table! Click on <Data<Pivot Table and
Pivot Chart Reports and follow that through. Pivot tables can be a bit
tricky until you get used to them with a bit of experimentation.

Regards.

Bill Ridgeway
Computer Solutions

"Aggie G" wrote in message
...
I have a spreadsheet with three sheets. One sheet is the main the data
sheet
that contains all the information. On the other two sheets I am trying to
setup something that would allow me to pull over data such as name, award
amount, award type, etc. based on the month awarded. I am not sure what
would
allow me to do this. Any direction?



  #3   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 13
Default Transfer information

A pivot table will not work for what I want. Let me provide you more
information.

Every month we have a staff meeting where employees are recognized and given
awards. The spreadsheet contains all the information about each award such as
the name of the recipient, the month award, the amount of time off, etc. I
want this information to populate on another sheet (Agenda), but I only want
the information associated with the month of the awared. As an example; if I
have the following:

Name Month Awarded Award Amount
Wylie, Amberlyn January-07 2
Boyett, Joann February-07 1
Brown, Josh February-07 2

Using this information, I want the name and the award amount for the month
of February to populate in the Agenda sheet. How can I do this?


ill Ridgeway" wrote:

Sounds like another job for Pivot Table! Click on <Data<Pivot Table and
Pivot Chart Reports and follow that through. Pivot tables can be a bit
tricky until you get used to them with a bit of experimentation.

Regards.

Bill Ridgeway
Computer Solutions

"Aggie G" wrote in message
...
I have a spreadsheet with three sheets. One sheet is the main the data
sheet
that contains all the information. On the other two sheets I am trying to
setup something that would allow me to pull over data such as name, award
amount, award type, etc. based on the month awarded. I am not sure what
would
allow me to do this. Any direction?




  #4   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 2,886
Default Transfer information

Hi

It sounds like Advanced Filter, extracting data to your Monthly sheet
will do the job.
Take a look at Debra Dalgleish's site for detailed instruction on how to
achieve it.
http://www.contextures.com/xladvfilter01.html#ExtractWs

--
Regards

Roger Govier


"Aggie G" wrote in message
...
A pivot table will not work for what I want. Let me provide you more
information.

Every month we have a staff meeting where employees are recognized and
given
awards. The spreadsheet contains all the information about each award
such as
the name of the recipient, the month award, the amount of time off,
etc. I
want this information to populate on another sheet (Agenda), but I
only want
the information associated with the month of the awared. As an
example; if I
have the following:

Name Month Awarded
Award Amount
Wylie, Amberlyn January-07 2
Boyett, Joann February-07 1
Brown, Josh February-07 2

Using this information, I want the name and the award amount for the
month
of February to populate in the Agenda sheet. How can I do this?


ill Ridgeway" wrote:

Sounds like another job for Pivot Table! Click on <Data<Pivot Table
and
Pivot Chart Reports and follow that through. Pivot tables can be a
bit
tricky until you get used to them with a bit of experimentation.

Regards.

Bill Ridgeway
Computer Solutions

"Aggie G" wrote in message
...
I have a spreadsheet with three sheets. One sheet is the main the
data
sheet
that contains all the information. On the other two sheets I am
trying to
setup something that would allow me to pull over data such as name,
award
amount, award type, etc. based on the month awarded. I am not sure
what
would
allow me to do this. Any direction?






  #5   Report Post  
Posted to microsoft.public.excel.setup
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Transfer information

One alternative play which would also deliver the required results
is illustrated in this sample construct:
http://www.savefile.com/files/646903
Reflect selected info in another sht.xls

Source table assumed in sheet: X, cols A to C,
with the key col = col B (Month Awarded) <contains "1st-of-month" real dates
Data assumed running in row2 down

In sheet: Agenda,
Create a DV to select the month-year in C2
(use a defined range: Month created in the index sheet: I)
Select C2, click Data Validation. Allow: List, Source: =Month

Then place

In A4:
=IF(X!B2="","",IF(X!B2=$C$2,ROW(),""))
Leave A1:A3 blank. This is the criteria col.

In B4:
=IF(C4="","",ROW(A1))
Col B is to provide a simple serial numbering corresponding to what's
extracted in col C

In C4:
=IF(ROW(A1)COUNT($A:$A),"",INDEX(X!A:A,SMALL($A:$ A,ROW(A1))-2))

In D4:
=IF(ROW(A1)COUNT($A:$A),"",INDEX(X!C:C,SMALL($A:$ A,ROW(A1))-2))
Cols C & D will extract the name and award amts from X's cols A and C, via
the indexed cols viz: INDEX(X!A:A,... & INDEX(X!C:C,...

Select A4:D4, copy down to cover the max expected extent of data in X's col
B, say down to D200?. Hide away col A. Cols B to D will return the required
results from X, depending on the month-year selected in the DV cell C2.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Aggie G" wrote:
A pivot table will not work for what I want. Let me provide you more
information.

Every month we have a staff meeting where employees are recognized and given
awards. The spreadsheet contains all the information about each award such as
the name of the recipient, the month award, the amount of time off, etc. I
want this information to populate on another sheet (Agenda), but I only want
the information associated with the month of the awared. As an example; if I
have the following:

Name Month Awarded Award Amount
Wylie, Amberlyn January-07 2
Boyett, Joann February-07 1
Brown, Josh February-07 2

Using this information, I want the name and the award amount for the month
of February to populate in the Agenda sheet. How can I do this?



  #6   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 13
Default Transfer information

I would like to send you the file I am working with b/c I am not able to
apply the sample you provided to my spreadsheet since they don't look alike.
I tried to change the formula to match with my information, but am finding it
difficult. I have a link to my document so you can maybe send me in the right
direction.

http://www.savefile.com/files/651790



"Max" wrote:

One alternative play which would also deliver the required results
is illustrated in this sample construct:
http://www.savefile.com/files/646903
Reflect selected info in another sht.xls

Source table assumed in sheet: X, cols A to C,
with the key col = col B (Month Awarded) <contains "1st-of-month" real dates
Data assumed running in row2 down

In sheet: Agenda,
Create a DV to select the month-year in C2
(use a defined range: Month created in the index sheet: I)
Select C2, click Data Validation. Allow: List, Source: =Month

Then place

In A4:
=IF(X!B2="","",IF(X!B2=$C$2,ROW(),""))
Leave A1:A3 blank. This is the criteria col.

In B4:
=IF(C4="","",ROW(A1))
Col B is to provide a simple serial numbering corresponding to what's
extracted in col C

In C4:
=IF(ROW(A1)COUNT($A:$A),"",INDEX(X!A:A,SMALL($A:$ A,ROW(A1))-2))

In D4:
=IF(ROW(A1)COUNT($A:$A),"",INDEX(X!C:C,SMALL($A:$ A,ROW(A1))-2))
Cols C & D will extract the name and award amts from X's cols A and C, via
the indexed cols viz: INDEX(X!A:A,... & INDEX(X!C:C,...

Select A4:D4, copy down to cover the max expected extent of data in X's col
B, say down to D200?. Hide away col A. Cols B to D will return the required
results from X, depending on the month-year selected in the DV cell C2.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Aggie G" wrote:
A pivot table will not work for what I want. Let me provide you more
information.

Every month we have a staff meeting where employees are recognized and given
awards. The spreadsheet contains all the information about each award such as
the name of the recipient, the month award, the amount of time off, etc. I
want this information to populate on another sheet (Agenda), but I only want
the information associated with the month of the awared. As an example; if I
have the following:

Name Month Awarded Award Amount
Wylie, Amberlyn January-07 2
Boyett, Joann February-07 1
Brown, Josh February-07 2

Using this information, I want the name and the award amount for the month
of February to populate in the Agenda sheet. How can I do this?

  #7   Report Post  
Posted to microsoft.public.excel.setup
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Transfer information

Here's your sample, with the suggestion implemented to suit:
http://cjoint.com/?etbDrKg51K
Aggie_Sample_File.xls

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Aggie G" wrote:
I would like to send you the file I am working with b/c I am not able to
apply the sample you provided to my spreadsheet since they don't look alike.
I tried to change the formula to match with my information, but am finding it
difficult. I have a link to my document so you can maybe send me in the right
direction.

http://www.savefile.com/files/651790


  #8   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 13
Default Transfer information

Thank you very much! It works perfectly and I can now use this for future
spreadsheets. Thanks again!

"Max" wrote:

Here's your sample, with the suggestion implemented to suit:
http://cjoint.com/?etbDrKg51K
Aggie_Sample_File.xls

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Aggie G" wrote:
I would like to send you the file I am working with b/c I am not able to
apply the sample you provided to my spreadsheet since they don't look alike.
I tried to change the formula to match with my information, but am finding it
difficult. I have a link to my document so you can maybe send me in the right
direction.

http://www.savefile.com/files/651790


  #9   Report Post  
Posted to microsoft.public.excel.setup
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Transfer information

You're welcome, Aggie.
Delighted to hear that.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Aggie G" wrote in message
...
Thank you very much! It works perfectly and I can now use this for future
spreadsheets. Thanks again!



  #10   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 13
Default Transfer information

Ok I have another question about this same spreadsheet. What I am trying to
accomplish is create an accumulative amount of awarded hours for each
employee in my company for each MTD and YTD. I want the Carrots YTD worksheet
to read from the Carrot 2007 worksheet and enter in the total hours for each
month and provide a total for the year. Can you help me do this? Below is the
link to my document.

http://www.savefile.com/files/651790


"Max" wrote:

You're welcome, Aggie.
Delighted to hear that.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Aggie G" wrote in message
...
Thank you very much! It works perfectly and I can now use this for future
spreadsheets. Thanks again!






  #11   Report Post  
Posted to microsoft.public.excel.setup
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Transfer information

In sheet: Carrots 2007 YTD,

Put in B2:
=SUMPRODUCT(('Carrot 2007'!$B$2:$B$100=$A2)*('Carrot
2007'!$E$2:$E$100=B$1),'Carrot 2007'!$G$2:$G$100)

Copy B2 across to M2, fill down. The simple SUM that you have in N2 down
(for YTD) suffices. For a neater look, we could suppress the display of
extraneous zeros in the sheet via clicking Tools Options View tab
Uncheck "Zero values" OK.

P/s: For the mutual benefit of all, kindly put in any new queries as **new**
postings.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Aggie G" wrote in message
...
Ok I have another question about this same spreadsheet. What I am trying
to
accomplish is create an accumulative amount of awarded hours for each
employee in my company for each MTD and YTD. I want the Carrots YTD
worksheet
to read from the Carrot 2007 worksheet and enter in the total hours for
each
month and provide a total for the year. Can you help me do this? Below is
the
link to my document.

http://www.savefile.com/files/651790


  #12   Report Post  
Posted to microsoft.public.excel.setup
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Transfer information

One clarification. Do adjust the ranges in the formula to suit the max
expected for the full year in the source sheet: Carrot 2007 before you copy
across/down. I used an arb row100 as the extent. Adjust to use the smallest
range large enough to cover the max expected extent, eg 500?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---


  #13   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 13
Default Transfer information

Thanks for the forumla. When I plugged in the formula, the cell displays a
REF error. What I did was press f2 and then hit enter and box appears for me
to select the file from where the data is supposed to pull from. If I
reselect the same file I am working in, the correct number appears. For
whatever reason, the formula is not recgnoizing that the worksheet is within
the same workbook. What should I do?

"Max" wrote:

In sheet: Carrots 2007 YTD,

Put in B2:
=SUMPRODUCT(('Carrot 2007'!$B$2:$B$100=$A2)*('Carrot
2007'!$E$2:$E$100=B$1),'Carrot 2007'!$G$2:$G$100)

Copy B2 across to M2, fill down. The simple SUM that you have in N2 down
(for YTD) suffices. For a neater look, we could suppress the display of
extraneous zeros in the sheet via clicking Tools Options View tab
Uncheck "Zero values" OK.

P/s: For the mutual benefit of all, kindly put in any new queries as **new**
postings.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Aggie G" wrote in message
...
Ok I have another question about this same spreadsheet. What I am trying
to
accomplish is create an accumulative amount of awarded hours for each
employee in my company for each MTD and YTD. I want the Carrots YTD
worksheet
to read from the Carrot 2007 worksheet and enter in the total hours for
each
month and provide a total for the year. Can you help me do this? Below is
the
link to my document.

http://www.savefile.com/files/651790



  #14   Report Post  
Posted to microsoft.public.excel.setup
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Transfer information

Here's your earlier sample, with the formula implemented:
http://cjoint.com/?eAgTyEMBRC
Aggie_Carrot_Program_Report_2007.xls

Usually, when you copy n paste formulas direct from postings into formula
bars, you need to clean up the pasted formula before pressing ENTER. There
would be some line breaks introduced into the pasted formula. Just clean up
with the Delete key or Backspace key as necess.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Aggie G" wrote in message
...
Thanks for the formula. When I plugged in the formula, the cell displays a
REF error. What I did was press f2 and then hit enter and box appears for
me
to select the file from where the data is supposed to pull from. If I
reselect the same file I am working in, the correct number appears. For
whatever reason, the formula is not recgnoizing that the worksheet is
within
the same workbook. What should I do?



  #15   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 13
Default Transfer information

Thanks so much! You have helped me so much! I appreciate your help!

Aggie

"Max" wrote:

Here's your earlier sample, with the formula implemented:
http://cjoint.com/?eAgTyEMBRC
Aggie_Carrot_Program_Report_2007.xls

Usually, when you copy n paste formulas direct from postings into formula
bars, you need to clean up the pasted formula before pressing ENTER. There
would be some line breaks introduced into the pasted formula. Just clean up
with the Delete key or Backspace key as necess.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Aggie G" wrote in message
...
Thanks for the formula. When I plugged in the formula, the cell displays a
REF error. What I did was press f2 and then hit enter and box appears for
me
to select the file from where the data is supposed to pull from. If I
reselect the same file I am working in, the correct number appears. For
whatever reason, the formula is not recgnoizing that the worksheet is
within
the same workbook. What should I do?






  #16   Report Post  
Posted to microsoft.public.excel.setup
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Transfer information

Pleasure` Aggie.
Thanks for the feedback
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Aggie G" wrote in message
...
Thanks so much! You have helped me so much! I appreciate your help!

Aggie



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
how do I transfer information from one sheet to another? Dan B. Excel Discussion (Misc queries) 2 April 14th 06 01:33 PM
How do I transfer formulas but not the information? Confused Assistant Excel Worksheet Functions 1 March 17th 06 05:05 PM
Transfer information into Excel spreadsheet Linda Excel Discussion (Misc queries) 0 January 18th 06 01:46 AM
How do I transfer information from Colums to Rows? ryanje Excel Discussion (Misc queries) 1 April 19th 05 06:48 PM
transfer information from a site into excel joe l Excel Discussion (Misc queries) 3 December 23rd 04 03:43 PM


All times are GMT +1. The time now is 01:59 PM.

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

About Us

"It's about Microsoft Excel"