Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Copy Formula to Non-Blank Cells


(!)

moose_ink's Avatar
moose_ink moose_ink is offline
Computer Specs
Junior Member with 4 posts.
THREAD STARTER
 
Join Date: Dec 2010
Experience: Intermediate
30-Dec-2010, 02:28 PM #1
Copy Formula to Non-Blank Cells
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
File Type: xlsx MarkupExample.xlsx (9.2 KB, 59 views)
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
30-Dec-2010, 03:23 PM #2
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's Avatar
moose_ink moose_ink is offline
Computer Specs
Junior Member with 4 posts.
THREAD STARTER
 
Join Date: Dec 2010
Experience: Intermediate
30-Dec-2010, 03:50 PM #3
Selection is Too Long
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!
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
30-Dec-2010, 04:00 PM #4
I don't understand your error description.

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's Avatar
moose_ink moose_ink is offline
Computer Specs
Junior Member with 4 posts.
THREAD STARTER
 
Join Date: Dec 2010
Experience: Intermediate
30-Dec-2010, 04:17 PM #5
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?
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
30-Dec-2010, 04:21 PM #6
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"?
moose_ink's Avatar
moose_ink moose_ink is offline
Computer Specs
Junior Member with 4 posts.
THREAD STARTER
 
Join Date: Dec 2010
Experience: Intermediate
30-Dec-2010, 05:15 PM #7
Looks like it's a limit of Excel versions 2000, 2003 & 2007. It was solved in 2010.

Here's more info on it: http://blog.contextures.com/archives...-is-too-large/

I just need to select smaller bits of data at a time. I selected 25,000 rows at a time and it worked fine.

Thank you for your help, you definitely provided the solution and I appreciate it!
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


Tags
formula, markup, non blank cells

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


Similar Threads
Title Thread Starter Forum Replies Last Post
Excel to ignore blank cells in a formula ms_r44 Business Applications 2 24-Nov-2010 04:44 PM
Solved: IF Formula to skip emply cells gevans Business Applications 6 13-Sep-2008 05:11 AM
Excel - List only non-blank cells separate page kal395 Business Applications 4 06-Aug-2007 10:54 AM
How to Remove Blank Cells-Excle Formula Nikil Business Applications 8 10-May-2007 09:33 AM
#DIV/0! Error How do I get formula to skip a cell where the formula returns a sum 0? jeffreysee Business Applications 6 28-Sep-2004 01:44 AM

WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑