paste linked cells show "0"

I have a number of cells in a range (lets say A14:J52).
Some of these cells are "paste linked" from cells on other sheets.

Unfortunately, when the original cell is empty, the value of the
"paste linked" cell shows as "0".
(not just blank like the original)

Is there a way to keep the "paste linked" cell showing "0"?

If not, how would I go about using VBA to systematically go through
the above described range and delete those zeros?

At the point I ran this code, any "0" could be deleted from the range
safely whether it was a "paste linked" cell or not.


Any help would be appreciated.


Doug
0
Doug
11/16/2009 6:49:26 PM
excel.programming 6508 articles. 2 followers. Follow

6 Replies
4719 Views

Similar Articles

[PageSpeed] 4

format the cell as #


On Nov 16, 11:49=A0pm, Doug Howell <douglasehow...@hotmail.com> wrote:
> I have a number of cells in a range (lets say A14:J52).
> Some of these cells are "paste linked" from cells on other sheets.
>
> Unfortunately, when the original cell is empty, the value of the
> "paste linked" cell shows as "0".
> (not just blank like the original)
>
> Is there a way to keep the "paste linked" cell showing "0"?
>
> If not, how would I go about using VBA to systematically go through
> the above described range and delete those zeros?
>
> At the point I ran this code, any "0" could be deleted from the range
> safely whether it was a "paste linked" cell or not.
>
> Any help would be appreciated.
>
> Doug

0
muddan
11/16/2009 7:14:13 PM
Changing the cell to number doesn't work.
It still shows "0" in the linked cell.
0
Doug
11/16/2009 8:37:22 PM
Format as Custom

Type the # sign only


Gord Dibben  MS Excel MVP

On Mon, 16 Nov 2009 12:37:22 -0800 (PST), Doug Howell
<douglasehowell@hotmail.com> wrote:

>Changing the cell to number doesn't work.
>It still shows "0" in the linked cell.

0
Gord
11/16/2009 8:42:53 PM
Too fast with the send button.

The custom format of # will hide the zeros but not show decimals if your
blank source cell becomes a number like 123.45

Rather than pasting links it is better, not easier, to use a formula like

=IF(Sheet1!A1="","",Sheet1!A1)


Gord

On Mon, 16 Nov 2009 12:42:53 -0800, Gord Dibben <gorddibbATshawDOTca> wrote:

>Format as Custom
>
>Type the # sign only
>
>
>Gord Dibben  MS Excel MVP
>
>On Mon, 16 Nov 2009 12:37:22 -0800 (PST), Doug Howell
><douglasehowell@hotmail.com> wrote:
>
>>Changing the cell to number doesn't work.
>>It still shows "0" in the linked cell.

0
Gord
11/16/2009 8:50:05 PM
Tools>Options>View>Zero Values.

That affects any zero, though - even the legitimate ones.
Can also use Conditional formatting - font & Cell the same colour
(white?).


On 16 Nov, 20:42, Gord Dibben <gorddibbATshawDOTca> wrote:
> Format as Custom
>
> Type the # sign only
>
> Gord Dibben =A0MS Excel MVP
>
> On Mon, 16 Nov 2009 12:37:22 -0800 (PST), Doug Howell
>
>
>
> <douglasehow...@hotmail.com> wrote:
> >Changing the cell to number doesn't work.
> >It still shows "0" in the linked cell.

0
AB
11/16/2009 8:51:23 PM
For Excel 2007, this is actually easier....

Excel Options -> Advanced -> Display Options for this Worksheet
uncheck the "show a zero in cells that have zero value" box


1
Doug
11/16/2009 8:54:51 PM
Reply:

Similar Artilces:

Abount Invoice in MSCRM 3.0
How to implement Instalments of Invoice? The Invoice created by the button "Create Invoice" on the "Contract" always have the total fee. Thanks for the reply! ...

Log files in Great Plains 8.0
We use a web based program to import orders from our website into Great Plains. We have recently come across some errors where invoices show up with no associated order or customer information. The only thing that shows on these invoices is item information. I believe these invoices are generated somehow by a glitch in the import we do for our web orders. Is there a log file somewhere in GP where I can see what information was entered based on date? Or if anyone else has experienced this problem before, am I correct in my assumption that the "ghost" invoices are being caused by ...

My Reports in GP 10.0
Hello: An end user of GP 10.0 has reported something intertesting to me on "My Reports" on the home page. If she switches companies (or when she first logs in at the beginning of the day) and clicks on "My Reports", she gets an "illegal address" error. The error is remedied by clicking "Customize this page", clicking the expansion button next to "My Reports", and checking all of the boxes next to her reports. Then, she does not get the error. But, it's kind of annoying for her to have to do this all of the time. Do you know wh...

how can I split a single cell diagonally in Excel 2000
Anyone out there know a way to split a single excel cell diagonally in order to have it contain 2 pieces of information? Forrest, As far as I know, you can not split a cell diagonally so that it can contain 2 pieces of information. However, you can merge cells which might give you the effect that you want. To do so, go to the standard toolbar and hit Format -> Cells -> Alignment Tab -> Text Control and work with the merged cells option. ---- Regards, John Mansfield http://www.pdbook.com "Forrest" wrote: > Anyone out there know a way to split a single excel ce...

CRM 3.0 Contact sync to outlook after migration
Hi, I have migrated a crm 3.0 sbs environment to a 4 server deployment. The customer have the requirement to sync all the account / contacts to the offline database. This is easily configured and works 100%. Now I can't seem to get the local CRM contact synced to the local outlook contacts :-( this doesn't happen and I have rechecked everything? Any suggestions? -- Warren O ...

Frontpage 2003 network link
Question: for the life of me I can't remember how to create a network link to my website so I can open it directly in Frontpage. Meaning... If I want to edit my webpage in frontpage 2003 directly instead of using my local copy of the website. How do I create a link on my computer and put it on my desktop? I, know this is probably an easy thing for you "brainies" but I can't remember how to do it. Please assist. There are two ways: Open FrontPage and use File->Recent Sites This option will keep the last four websites opened. Opening the website in FrontPage ...

Microsoft Dynamics CRM 3.0 Implementation in 2007
http://microsoft-crm-3.blogspot.com/ ...

Event ID 5896 after upgrade from 1.2 to 3.0
As the subject, I upgraded from 1.2 to 3.0, and now everytime a mail passes through I get the following error in the Application Event: Source: CrmExchangeQueueServ Event ID: 5896 Description: An HTTP Status of '404 - Not Found' occurred while attempting to deliver a message. Please check the URL in the registry. Message subject: 'subject of mail' URL: 'http://servername/MSCRMServices/CrmEmail.srf' Checked the location above, and sure enough - there is no CrmEmail.srf file, so it throws a 404 error.. great, only - why is this file missing?!! I presume it didn&#...

Copy/Paste with Hidden Rows/Columns Excel 2007
I am working with rather large & detailed spreadsheets. I have hidden rows and columns with supporting details that feed into the viewable results (formulas). I need to take the “zipped up” data and copy/paste to a new worksheet. When I paste the data, it appears with all of the hidden columns and rows visible one again. Is there any way to paste the zipped up data without the reappearance of the previously hidden rows and columns? Can you paste a link without all of the hidden data reappearing? Select range to copy which includes hidden rows and columns. F5>Special...

How do I link pictures in visio 2003?
I have a diagram of a room. I want to link blocks (squares with the name of a specific rack) to a diagram of that specific rack? If the rack diagrams are in different files than the room diagram, you could use hyperlinks. If all the diagrams are in the same file, you can use either hyperlinks or set the double-click behavior to jump to a specific page. -- Mark Nelson Microsoft Corporation This posting is provided "AS IS" with no warranties, and confers no rights. "Ron" <Ron@discussions.microsoft.com> wrote in message news:599AE588-8640-4D5C-B66C-7001D92FD5A...

Display nothing in a cell if value = 0
I have a range of cells formatted to display numbers in currency format. They have a formula applied to them so if the value of the Cell is nothing I get �0.00 showing. I would rather that nothing was shown in these instances. How is this achieved? Thanks Dom Couple of ways, use conditional formatting and display with a white font if the value is zero change the display option to suppress zeroes, Tools>Options>General and uncheck the Zero Values option. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mail...

Problems with MSDN Browser (VC++ 6.0)
Hello, using VC++ 6.0 and the MSDN Browser that ships with it I have the problem that the browser does not resolve all links properly. For example I cannot open any example from the MSDN CD. Since the browser is actually HH.EXE I guess that this behaviour has something to do with the IE security settings. Lowering the IE security settings is not an option, however I really would like to use the help in the way it was designed. Is there any known solution for this problem? BTW. there is a difference in behaviour if I call the help from the Windows Start Menu or from within Visual Studio. Any ...

CRM3.0 Showing Tabs in Different Forms.
Greetings. Is there a way I can have a custom tab flow/shared with another form? Specifically; I have a tab called “Company Profile” in the Lead form. Once the lead is converted to an Account, I would like to have the same Tab be available on the Account form with previously entered information & ability to add more. Do I just need to share the Tab ID from the Account Form to the Lead form or is there an easy way to grab attributes from another entity? Lidy, I hope somebody from the dev team is listening because that would be an awesome idea: share a Form/Tab/Section, as a read on...

4.819435E-02 Maybe We Can Help Each Other ... 0.8706568
0.1376901 Maybe We Can Help Each Other ... 0.3365903 4.819435E-02 Now Visit http://www.clicklinknow.com/ss/ 0.8706568 ...

CRM 3.0 Adding Custom Fields
I'm trying to add a number of fields I created for the Account Form. When I choose Add Fields I can check the fields I want to add but there is no OK button to actually add them. Has anybody else ran into this? ...

POS 2.0
How do you receive case of soda and sell in case lot, 12 pack, six pack and single? Does POS 2.0 have "parent items"? Yes there are parent items - create the child item & then go to the properties for this item - special tab - set parent item - set child quantity "frabs" wrote: > How do you receive case of soda and sell in case lot, 12 pack, six pack and > single? Does POS 2.0 have "parent items"? This is a multi-part message in MIME format. ------=_NextPart_000_0253_01C82AD1.E1E127E0 Content-Type: text/plain; charset="utf-8" Conte...

Why does Excel display 01/12/2005 as 38364 when the cell value is.
Excel 2000 and 2003 displays 01/12/2005 as 38364 even though the cell value is 01/12/2005. How do I change this? If it matters, this is a shared workbook stored on a network drive. XL stores dates as integral offsets from a base date. 01/12/2005 happens to be 38,364 days after 12/31/1899. To see the date format again, choose Format/Cell/Date... In article <4619432F-994C-417B-81B6-17372478C9CB@microsoft.com>, "Chris" <Chris@discussions.microsoft.com> wrote: > Excel 2000 and 2003 displays 01/12/2005 as 38364 even though the cell value > is 01/12/2005. H...

Windows CE 6.0 vs Windows CE 5.0
Hello, I have ported my BSP from Windows CE 5.0 to Windows CE 6.0 R3. I have an application developed with .Net CF 2.0 that runs on my target device. I have observed that there aren't performance improvements passing from Windows CE 5.0 to Windows CE 6.0 R3. My application response times are the same. Is it normal ? Windows CE 5.0 R3 has more reliability and a differente memory model architecture / driver architecture but it hasn't performance improvements ? Thanks, Paolo -- Paolo Patierno Embedded Software Engineer On 4/9/2010 9:23 AM, Paolo Patierno wrote: &...

OT
I'm not having much luck with Google and I realize it's off topic in here, but maybe someone can point me in the right direction for help. In Winzip 12.0 when I open a zip file with pictures in it, it always brings them up in thumbnail view. I can change the view to "details" and get a list of the filenames which is what I want to be the default action. But I can't seem to find a place where I can set that to the default. Winzip 8.0 didn't do this. I was *happy* with 8.0 but unfortunately it isn't compatible with Vista or Windows 7 -- ...

Business Portal 2.0 and SQL Reporting Services
Hello - I just recently installed Business Portal on a new server and then installed SQL reporting services on the same box. The installation of SQL reporting services broke Business Portal! I can get to http://server/reports but not http://server/sites/BusinessPortal. What do I need to get these both to work on the same box? Thank You!! Good Morning!! What error message do you get when you try to browse to Business Portal? I don't know much about SQL Reporting Services, but do you have to select a website for it to run? Thanks, Rekoj "GreenThumb" wrote: > Hell...

LDAP screen help???
Just installed Outlook 2003. When I launch I get the attached screen. How do I get this screen to go away?? Thanks in Advance, Larry Tools->email accounts->directories->View or change->remove the LDAP directory from the list that appears. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After searching google.groups.com and finding no answer: XT <xt50@home.net> asked: | Just installed Outlook 2003. When I launch I get the attached screen. | How do I get this screen to go away?? | | Thanks in Advance, | Larry ...

ERROR when loading GP 10.0
I am not able to install/run GP 10.0 on a single machine. I have installed many instances of GP10(connecting to the same server) before and have never seen this issue. I will load GP and when I try to access the application by double-clicking the icon, GP will act like it is loading, but in the Taskbar, where it should say GP 10.0, it says 'ERROR'. GP will then just sit there until you kill it in TaskManager. I have re-installed, repaired, checked ODBC connections to no avail. Has anyone seen this issue before? I'm leaning towards a Registry problem, but am just spec...

CRM 4.0 Redeployment
Hello, How is it possible to install CRM and connect to existing DB when you want to install on a new OU, when you're never asked which OU to use? U need to provide complete information : CRM Version : Type = professional , enterprise..... If you are using enterprise edition then u can import it (in CRM 4.0). In other cases organizationsunit name should be same as MSCRM database name. ------ Aamir Blog = http://mscrmsupport.wordpress.com/ Trond: 1. Install CRM 4.0 as a 'new' installation, choose the OU you want during the installation. when it asks for an organization n...

CONCATENATE involving CHAR(13) and CHAR(10): "" marks when copy/paste to text editor [???]
The result of this formula: =CONCATENATE("#1=",W4,CHAR(13),CHAR(10),"GOTO665",CHAR(13),CHAR(10),"N5",Y4) has quotation marks when copy pasted into a text editor, like so: "#1=2 GOTO665 N553" You can see that the CR/LF comes out but it adds the quotation marks. Is there any cure for this? Thanx, -plh PS: I tried using only CHAR(13) in place of CHAR(13),CHAR(10) and also CHAR(10) only, and also using cell references in place of the literal strings, that is: =CONCATENATE($Y$1,W3,CHAR(13),CHAR(10),$AA$1,CHAR(13),CHAR(10),$Z $1,Y3) but the result was the sam...

Any issues with Windows Service Pack 3 (XP) and GP v. 8.0? Thanks
My client is looking to see if there are any issues with this combination. Thank you. No issues that I have heard about. However, check the system requirements page as it is updated frequently to address some of these questions at https://mbs.microsoft.com/partnersource/products/greatplains/documentation/systemrequirements/compatibility_gp8.0_lowmidhi.htm?printpage=false Just make sure you are running the latest GP 8 service pack (Service Pack 5) available at https://mbs.microsoft.com/partnersource/products/greatplains/downloads/servicepack/greatplains80_servicepacks.htm?printpage=fal...