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
4568 Views

Similar Articles

[PageSpeed] 51

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:

cannot modify linked tables in A 2003
In a front-end/back-end application created in A 2000 I cannot modify or add data in the linked tables if I open the front-end in A 2003. In A 2002 everything still worked fine. Refreshing the links using the linked table manager does not solve the problem. Who has experienced the same problem and found the solution? Hello, Try adding a primary key in the back-end database and then refresh the links. Ki ...

How do I set up a chart to only chart values greater than 0
I have a chart in Excell that I would like to only have chart those items with a value of greater than 0 (zero). Not sure if there is a better, easier way than having manually manipulate the data. Let's say the values are in B1:B20 In C1 enter =IF(B1>0,B1,NA()) Now use column C for the chart; the displayed #N/A values will be ignored by the chart engine best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "debra49424" <debra49424@discussions.microsoft.com> wrote in message news:A513E053-4AD9-44FE-952E-E9A60F49...

Will excel 2003 format a cell to the year?
It seems like it should be obvious, but I am finding it impossible to get excel to do a date format that recognizes a year. Is there some way? I typed a date in A1 (09/28/2006) and used format|Cells|number tab|custom category|yyyy with my USA settings. kateofmd@msn.com wrote: > > It seems like it should be obvious, but I am finding it impossible to > get excel to do a date format that recognizes a year. Is there some > way? -- Dave Peterson ...

How to create email template for campaign response in CRM 4.0 ?
Hi all, I am trying to create a workflow to send email (using a template) after create a campaign response but it fails ( Show "invalid Argument" error ). I have also tried to do the same workflow for campaign and fail too. Is CRM 4.0 not supported using email template to send mail thru workflow ? Thanks for help ! ...

Outlook 2003: Links cannot be viewed
Hyperlinks in messasges are not functioning properly. Instead of taking me to a website, etc., when I click on a link, a dialog box appears asking me to save the file as an executable. Any clue on how to get hyperlinks in Outlook functioning properly? I have already performed a "repair" from the Office installation CD to no avail. Thanks in advance! Mike ...

GP8.0 Upgrade Problems
I am trying to upgrade a test server to GP8.0 SP3 from GP7.5 SP6. I have run into several issues, and although I have resolved some of them (i.e. can't have IDENTITY column in any tables that's not named DEX_ROW_ID), there's a few I am still having trouble with. I am installing GP8.0 into a separate directory on the server. 1) When I run the Perform Special Upgrade in GP Utilities after installing SP3, I get a "Invalid column name DAYTOEXP" error. Looking at a SQL profiler trace of this, I can see that it is failing when recreating the IVR10015 table. I checked our live ...

CRM 3.0 Tax Calculation
I tried to automate tax calculation for the Quote Product form (can also be the Order- or Invoice Product form, principle is the same) in an onChange event, simplified like this: crmForm.all.tax.DataValue = crmForm.all.baseamount.DataValue * TaxRate; The problem is that the field crmForm.all.baseamount.DataValue is updated after a Save or SaveAndClose, so after a possible onChange event. Also using the onSave event of the form does not work, the crmForm.all.baseamount.DataValue is calculated or updated after the event (?). Question: How/Where are the fields crmForm.all.baseamount.DataV...

Accounting format for 0
I have had the same problem in both Excel 1997 and Excel 2000. I always use the Accounting format which gives a dash for a 0. However, every once in while I will get a 0 instead of a dash. When I do that I extend out the decimals and way out there are some numbers. I always check to make sure that any formulas I have either round or are from whole numbers so there is no possibility of it being a rounding problem. I think it's probably a glitch in the Excel program, but I haven't talked with anyone else who has encountered this. Mainly I think because most people don'...

Copying from 2007 (past 65Kth row) to 2003
Fill in 4 cells in 2007, A300000:B300001, putting in the values 1,2,3,4 to easily identify them. Select and copy the (square) range of 4 cells. Open 2003 and paste to cell A3. Interestingly, only the contents of (2007's) A65536:B65536 will be pasted to 2003's cell A3. (or any destination in 2003). To verify, put =row() & "C" & column() in A65536:B65536 and repeat. Was this patched or something...or do you duplicate the behavior? Same machine with dual 2003/2007 installation. 2003 is version SP2. I can't tell you with my 2007. (Yes, that's right. 2007's...

Trying to get a blank cell presented if B26=0
IF B26=0, how do I present a blank cell in another place, or put an amount if not true. I have used =IF(B26=0,0,Sheet1!E2) , but I get a $0.00 figure which I do not want. Even I if I used a blank cell for the true portion, I get then same $0.00 figure. I get the right amount for false. I am preparing an excel program for a small plowing business. Hi, Try this =IF(B26=0,"",sheet1!E2) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. ...

CRM Reports
Does anyone w/ experience in both CRM v1.2 and v3.0 know if the standard reports in v3.0 are the same (if not better) as what was in v1.2? Were any left out of v3.0 that were part of v1.2? -- Thanks. RE The standard reports are very different. From memory, there were about 120 reports in CRM 1.2 - there are only about 20 in CRM 3.0. Also, Crystal reports is no longer the reporting engine - Microsoft have moved to SQL Reporting Services. If you're doing an upgrade you can still use the old reports, as long as you have a licensed version of the appropriate CR software. "RE&qu...

CRM Version 2.0
I see several features that I'm looking for are planned for version 2.0. Can anyone tell me when this version is due to be released? ETA Q1 2005 Frank Lee Workopia, Inc. San Francisco, CA >> Other Microsoft CRM Online Forum Resources: http://www.workopia.com/Links.htm >-----Original Message----- >I see several features that I'm looking for are planned >for version 2.0. Can anyone tell me when this version is >due to be released? >. > Latest "hope for" date is Q2 2005, however that is not a set date... John. "Terri" <anonymo...

Clicking web links in Outlook 2003
Hi, In O2K, clicking multiple web links (inside the emails) re-uses the same IE window. In O2K3 this opens an IE window for each link clicked. Is there any way to get O2K3 to behave like O2K? Thanks, FW ...

Regarding bluetooth HFP support on Wince 6.0
Hi CEAssist, Thanks in advance. I am vinayakumarc, i have developed bluetooth application on wince 5.0 with Navready patch installed previously. Now i want to port the same application on the Wince 6.0, is it possible? Does Wince 6.0 support Bluetooth Hands free profile & PBAP?Which version of BSP should i use? 1.4 or 1.5. regards, ...

Great Plains integration to CRM 4.0
does anyone know if there is integration of great plains with crm 4.0? we have crm 3.0 integrated to great plains 8.0 and want to upgrade both. Hi Shawn, There is no Microsoft connector yet for CRM 4 and GP. There is one for CRM 3 and GP. Other options are to use • some third party tools like Scribe • or use Microsoft BizTalk • or do custom Programming. -- uMar Khan :: MS CRM MVP Microsoft CRM Consultant Email :: imumar at gmail dot com Blog :: http://umarkhan.wordpress.com MVP :: https://mvp.support.microsoft.com/default.aspx/profile/umar.khan "Shawn" wrote: > does an...

How to migrate data from Act!9 to CRM 4.0
We just a new customer who is using Act!9 now. We need to move data from Act!9 into CRM 4.0. The data includes contacts, notes, history, activities of Act!. It is easy to use DMM of CRM 4.0 to import contats. But there is no exporting for notes, history, activities of Act!. Now looks I have two choices, 1. use a 3rd tool to export ACT!9 notes, history, activities, import into CRM by DMM. 2. use Scribe to do it for option 1, anyone knwo which tool I can use for ACT!9? For option 2, which Scribe part I should buy? how to use it? thanks Andrew I'd use Scribe's 45-day license for ...

format of go to cells
using excel 2007, using find-go to special formulas, the shading on the formula cells is the lightest blue and very difficult to see. Can I change this fill color format? charlie Unfortunately I don't think you can. There have been a lot of complaints about shading of cells in XL2007. -- Regards Roger Govier "Charlie Woll" <cwoll@citlink.net> wrote in message news:eiB2opYcIHA.3572@TK2MSFTNGP02.phx.gbl... > using excel 2007, using find-go to special formulas, the shading on the > formula cells is the lightest blue and very difficult to see. Can I > cha...

Prefiltering CRM 3.0 Reports
Hi, I'm hoping someone can throw some light onto this one! I have a CRM 3.0 installation running on a Windows 2003 Small Business Server with SQL 2005 and reporting services (2005). I create a report using Visual Studio 2005 and the Report Wizard, to list all of the active accounts. I upload the report using CRM. The report runs fine, until i want to filter it. So, back in Visual Studio i create an alias CRMAF_TableName. And re-upload the report. The report still runs, but it will not let me filter the report. I could do with filtering the report. I read somewhere that i might ne...

Problem showing full category list after upgrade from Money 03 to
I just upgraded from Money 2003 to 2007. When I select : Microsoft Money Settings -> Category settings -> Show Full Category List I receive the following error message: Money encountered an error and must cancel this operation. Please try again later. Is there any way to find out more about what is going wrong? ...

3.0 client crashes when going offline?
this user hasnt had any problems since we implemented 3.0 about a year ago. they are using Office 2007 with the 3.0 client in offline configuration. A couple days ago they ran into a problem where it crashes when trying to go offline, it gets to "waiting for offline database initialization" and then crashes outlook. any ideas? Have you verified that the localo SQL instance is happy and checked the event logs? "rodney" wrote: > this user hasnt had any problems since we implemented 3.0 about a year > ago. they are using Office 2007 with the 3.0 client in offl...

Outlook 2007 cut and paste double spacing
When I cut lists from a text file I use and paste them into the body of an Outlook message, the list becomes double spaced. I have to highlight the text and then use the single space format button. Is there a way to paste the lists in without them being automatically double spaced? Thanks, K Are you pasting into a double spaced or single spaced message? Which paste option do you use? as text should use the spacing of the paragraph you are pasting into. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://...

CRM 3.0 Campaigns
Hi, Can anyone point me to a detailed article highlighting the steps/ functionality of a how to create, deploy, manage, a CRM Campaign? I have looked but most articles just talk about possibilities without any groundwork on how to get there. Thanks, Matt maybe this will help: http://download.microsoft.com/download/1/3/b/13bff997-8c18-4f7b-a87b-d7bebc95ad4d/Microsoft%20Dynamics%20CRM%203%20Marketing%20Automation.pdf Koren Castor Advanced Solutions http://www.castorsoft.com "matts@brodaseating.com" wrote: > Hi, > > Can anyone point me to a detailed article highlightin...

How do I link to an Item within a Public Folder in MS Outlook 2000SR-1?
Title says it all. -- Regards. Chris. Remove MAPSON to reply to me directly. This post represents the views of the author and does not necessarily accurately represent the views of BT. ...

Upgrading MS CRM 3.0 to MS CRM 4.0
We have an MS CRM 3.0 setup and running well for quite long time. We would like to upgrade it to MS CRM 4.0. Current setup is, 1. Lot of reports added & customized for user needs 2. Lot of entities added & customized for user needs 3. Lot of forms added & customized for user needs 4. Lot of custom developed pages added (developed in ASP.NET 1.1). Of course it's running on separate site but integrated inside the MS CRM 3.0 (so typically used MS CRM 3.0 service calls) 5. Lot of Stored Procedure's & View's added 6. Customized the settings based on user needs My do...

Second GAL won't show up in OUTLOOK 2003
Hi guys, I run exchange 2003, upgraded from 5.5. 1 Information Store, 1 Server. I have 1 Global Address List. I have created a another one, but it wont't show up in the OUTLOOK under drop down menu next to Global Address List. I have waited for like a week for it to update and also run RUS. What could be the problem? Thanks! As far as I know a user onlt gets one GAL. You can control which one they get, but I don't believe you can make multiple ones available. -GT "HHRP" <HHRP@discussions.microsoft.com> wrote in message news:3E35DAAE-393D-4184-B1A8-978CA58A678...