Microsoft Office Forums Formula Paste Issue

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-01-2014, 02:11 AM
rabend rabend is offline Formula Paste Issue Windows 8 Formula Paste Issue Office 2013
Novice
Formula Paste Issue
 
Join Date: Jun 2014
Posts: 1
rabend is on a distinguished road
Default Formula Paste Issue

I ran into the following problem in EXCEL 2010 and 2013:

- I have a very large file that is roughly 43,000 rows long
- I filtered the 43,000 rows to yield only specific types of items
- In a column to the right of the filtered data I pasted a relative formula in each row that added 3 at each row
- The quantities of items in each row was 3, so I thought the paste would yield a sum of all filtered items
- The formula paste actually took place in all 43,000 rows instead of just the visible filtered rows yielding an erroneous total (much larger)


- At least I think it is erroneous.

Should Excel paste the formula even in rows that have been filtered out and are no longer visible? If so, does that make sense? I suspect I am missing something, and would appreciate any inputs.

Thanks,
Reply With Quote
  #2  
Old 06-01-2014, 12:05 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Formula Paste Issue Windows 7 64bit Formula Paste Issue Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,387
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

Perhaps use the SUBTOTAL function with finction_number 109 ?
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #3  
Old 06-02-2014, 11:22 AM
BobBridges's Avatar
BobBridges BobBridges is offline Formula Paste Issue Windows 7 64bit Formula Paste Issue Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 614
BobBridges will become famous soon enoughBobBridges will become famous soon enough
Default

Peco, did you mean to post that reply to a different question? I don't understand what it means in this context.

(In fact, I don't know what "finction" is.)

Rabend, I don't use the filter much but as I understand it it's intended to be a way of viewing details; as you're saying yourself, it doesn't have an effect on the SUM function, nor on any other function so far as I know.

I take it you want a sum of only those 3s that appear in certain cells, and you can do that but you'll need either SUMIF or COUNTIF. Do you know already how to do that, or shall I explain?

And by the way, as I said I don't use filters much so if someone else here knows that I'm mistaken about them not affecting functions, please chime in.
Reply With Quote
  #4  
Old 06-02-2014, 12:06 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Formula Paste Issue Windows 7 64bit Formula Paste Issue Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,387
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

"finction" was a typo for "function" As far as I understood OP is trying to sum filtered data which can be done with SUBTOTAL
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Paste to a series of boxes with one paste? BudVitoff Misc 0 11-27-2013 02:51 PM
Formula Paste Issue Paste Special: Copy and Paste Formatting Only? tinfanide Word 6 03-06-2013 12:21 AM
Formula Paste Issue Cut & Paste issue gbaker Excel 4 06-21-2012 08:10 AM
Formula Paste Issue Word 2007 Copy/Paste Excel issue raven26c Word 1 11-18-2011 02:49 AM
Issue with formula's from an outside source phroche Excel 0 11-07-2008 03:08 PM


All times are GMT -7. The time now is 03:39 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft