1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Copy Formula to Non-Blank Cells

Discussion in 'Business Applications' started by moose_ink, Dec 30, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. moose_ink

    moose_ink Thread Starter

    Joined:
    Dec 30, 2010
    Messages:
    4
    I'm formatting an Excel 2007 sheet on a Windows 2003 Server that needs to mark up values. These values are in rows that include blank cells in between cell values. How do I copy a formula to ONLY non-blank cells to mark up the values by, say 1.50?

    I've attached an example of what I'm working with. This includes how I've tried to copy a simple formula of =A2*1.5 into Column B, but it shows zeros where the Column A cells are blank.

    Thanks
     

    Attached Files:

  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,271
    Delete column B. Copy column A, paste at B1. Select column B. Press F5, click "Special", select "Constants", click OK. Type:

    =A1*1.5

    , press CTRL+Enter. Delete B1.

    HTH :) (welcome to the board)
     
  3. moose_ink

    moose_ink Thread Starter

    Joined:
    Dec 30, 2010
    Messages:
    4
    Thanks for the help. After clicking OK to the GO To Special "Constants", it replied an error that the "Selection was too long". I tried selecting only the rows needed (there are 51,870 rows) and saving it as a .xlsx file, but the same results.

    Any suggestions?

    Thanks!
     
  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,271
    I don't understand your error description. :D

    Erm ...


    ... select B2:B(last value in A). Type:

    =if(A2="",#n/a,A2*1.5)

    , then press CTRL+Enter. Without changing the selection: F5, Special, Formulas (Errors), OK. And press the Delete key.

    Any better? :)
     
  5. moose_ink

    moose_ink Thread Starter

    Joined:
    Dec 30, 2010
    Messages:
    4
    OK, I got your first suggestion to work on the sample I gave you. The problem now is that is only a very small sample of the data I'm working with. There are 51,870 rows total.

    When I try your first suggestion, I get a message saying "Selection is too Large" after clicking CTRL+Enter

    First suggestion:
    Delete column B. Copy column A, paste at B1. Select column B. Press F5, click "Special", select "Constants", click OK. Type:

    =A1*1.5

    , press CTRL+Enter. Delete B1.


    Is this a limit with the # of rows I can apply the GO TO Special funtion to?
     
  6. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,271
    I'm running Excel 2000. I tested "the GO TO Special function" on an entire column (c. 65K rows) with no prob. May I respectfully suggest you're not doing it exactly as "instructed"?
     
  7. moose_ink

    moose_ink Thread Starter

    Joined:
    Dec 30, 2010
    Messages:
    4
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/971620