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 |
#11
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
Transfer information
Pleasure` Aggie.
Thanks for the feedback -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Aggie G" wrote in message news Thanks so much! You have helped me so much! I appreciate your help! Aggie |
|
Thread Tools | |
Display Modes | |
|
|