A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Setting up and Configuration
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Transfer information



 
 
Thread Tools Display Modes
  #11  
Old April 26th, 2007, 12:27 AM posted to microsoft.public.excel.setup
Max
external usenet poster
 
Posts: 8,574
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  
Old April 26th, 2007, 03:56 AM posted to microsoft.public.excel.setup
Max
external usenet poster
 
Posts: 8,574
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  
Old April 26th, 2007, 04:44 AM posted to microsoft.public.excel.setup
Aggie G
external usenet poster
 
Posts: 16
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  
Old April 26th, 2007, 05:54 AM posted to microsoft.public.excel.setup
Max
external usenet poster
 
Posts: 8,574
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  
Old April 26th, 2007, 03:20 PM posted to microsoft.public.excel.setup
Aggie G
external usenet poster
 
Posts: 16
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  
Old April 26th, 2007, 03:43 PM posted to microsoft.public.excel.setup
Max
external usenet poster
 
Posts: 8,574
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:13 AM.


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