The Straight Dope

Go Back   Straight Dope Message Board > Main > General Questions

Reply
 
Thread Tools Display Modes
  #1  
Old 06-10-2010, 09:44 AM
mack mack is offline
Guest
 
Join Date: Oct 2000
Excel: formula doesn't work if I paste it in but works if I type it in

I just need to subtract date column G from date column H to get the number of days. I've entered the formula by hand and used DAYS360 but in either case if I copy the formula and paste it to another cell in the same column the formula will be updated with the appropriate cell references but the formula will return the same value that was the result of the cell I copied from. If I manually enter the formula it works fine. I have 2000+ rows I need this for so manually entering isn't an option. This is in excel 2007. Any ideas? Thanks!
Reply With Quote
Advertisements  
  #2  
Old 06-10-2010, 09:45 AM
Chessic Sense Chessic Sense is offline
Guest
 
Join Date: Apr 2007
Quote:
Originally Posted by mack View Post
I just need to subtract date column G from date column H to get the number of days. I've entered the formula by hand and used DAYS360 but in either case if I copy the formula and paste it to another cell in the same column the formula will be updated with the appropriate cell references but the formula will return the same value that was the result of the cell I copied from. If I manually enter the formula it works fine. I have 2000+ rows I need this for so manually entering isn't an option. This is in excel 2007. Any ideas? Thanks!
Do you have it set to manually update? Try turning that off.
Reply With Quote
  #3  
Old 06-10-2010, 09:46 AM
mack mack is offline
Guest
 
Join Date: Oct 2000
Holy crap, what was that, 5 seconds?

That did it. Thanks!

Last edited by mack; 06-10-2010 at 09:49 AM..
Reply With Quote
  #4  
Old 06-10-2010, 01:16 PM
Munch Munch is offline
Guest
 
Join Date: Mar 2000
Quote:
Originally Posted by mack View Post
if I copy the formula and paste it to another cell
Are you aware of how to drag a cell to copy it across a large number of cells (like a column of 5000 rows)?

I'm trying to figure out WHY this might have happened. Do you have any cells merged?
Reply With Quote
  #5  
Old 06-10-2010, 02:03 PM
mack mack is offline
Guest
 
Join Date: Oct 2000
Yes, I tried drag and drop. It didn't work.

Like Chessic Sense suggested, I went to Excel Options and switched from Manual to Automatic under calculation options. It works fine now.

What I don't understand is why Excel would allow a pasted formula to update it's references, but not output a correct result, as what happened in Manual mode. If it's not going to work properly, they should simply refuse the paste and bring up a message window. Very strange. I imagine there's a good reason for it, though.

Last edited by mack; 06-10-2010 at 02:05 PM..
Reply With Quote
  #6  
Old 06-10-2010, 02:55 PM
Munch Munch is offline
Guest
 
Join Date: Mar 2000
Gotcha. I don't know either - that's the first time I looked at that "feature", and can't think of an application for it either.
Reply With Quote
  #7  
Old 06-10-2010, 03:02 PM
tim-n-va tim-n-va is offline
Guest
 
Join Date: May 2007
It works that way because you want formulas to copy consistently regardless of the recalculation mode. You turn recalculation to manual to when you have a very complex spreadsheet that takes a long time to refresh.
Reply With Quote
  #8  
Old 06-10-2010, 03:06 PM
UncleRojelio UncleRojelio is offline
Member
 
Join Date: Nov 2004
Location: ATX
Posts: 5,339
I guess it doesn't matter now but in cases like this I always use 'Fill Down' and that seems to work correctly every time.
Reply With Quote
  #9  
Old 06-15-2011, 05:20 AM
manojharisree manojharisree is offline
Guest
 
Join Date: Jun 2011
Thank you!

Quote:
Originally Posted by Chessic Sense View Post
Do you have it set to manually update? Try turning that off.
That helped me too.

Thanks to Google Search and you

regards,
Manoj(Bangalore, India)
Reply With Quote
  #10  
Old 06-15-2011, 11:58 AM
ashtayk ashtayk is offline
Guest
 
Join Date: Aug 2000
Actually it might be a better idea to let it be on manual update.

If you have 2000 rows, every time you even filter a column, the sheet will recalculate and slow things down. Once you are done with pasting, filtering and what have you, just hit F9 (Function Key 9) - and the workbook will do a recalculation.
Reply With Quote
Reply



Bookmarks

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

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump


All times are GMT -5. The time now is 04:50 AM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.

Send questions for Cecil Adams to: cecil@chicagoreader.com

Send comments about this website to: webmaster@straightdope.com

Terms of Use / Privacy Policy

Advertise on the Straight Dope!
(Your direct line to thousands of the smartest, hippest people on the planet, plus a few total dipsticks.)

Publishers - interested in subscribing to the Straight Dope?
Write to: sdsubscriptions@chicagoreader.com.

Copyright 2013 Sun-Times Media, LLC.