Copy Formula to Non-Blank Cells

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 

Attachments

Joined
Jul 1, 2005
Messages
8,546
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)
 

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!
 
Joined
Jul 1, 2005
Messages
8,546
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? :)
 

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?
 
Joined
Jul 1, 2005
Messages
8,546
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"?
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Top