If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
Transfer information
Sounds like another job for Pivot Table! Click on DataPivot 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
|
|||
|
|||
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 DataPivot 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
|
|||
|
|||
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 DataPivot 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
|
|||
|
|||
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 A44, 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
|
|||
|
|||
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 A44, 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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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! |
|
Thread Tools | |
Display Modes | |
|
|