Excel inserted 0's in cells linked to blank cells
 

I added a work sheet by copying selected data from main worksheet (copy,
paste special, link) and where there were blank cells in original
worksheet, Excel inserted 0's. Is there a quick way to get rid of them.
Blank cells are to be expected in this database of names, info, etc.

My approach was to turn on auto filter and select for 0, highlight
column and delete contents. Hoping there is a less tedious way to do
this.

Thanks,
Trudy


--
lburg801
------------------------------------------------------------------------
lburg801's Profile: http://www.excelforum.com/member.php...o&userid=28338
View this thread: http://www.excelforum.com/showthread...hreadid=480045

Excel inserted 0's in cells linked to blank cells
 

lburg801 Wrote:
I added a work sheet by copying selected data from main worksheet (copy,
paste special, link) and where there were blank cells in original
worksheet, Excel inserted 0's. Is there a quick way to get rid of them.
Blank cells are to be expected in this database of names, info, etc.

My approach was to turn on auto filter and select for 0, highlight
column and delete contents. Hoping there is a less tedious way to do
this.

Thanks,
Trudy

Hi Trudy

2 Options:

Option 1: Tools Options View Tab Window Options, click in the
zero values box to remove the tick, this will suppress all zeros'
problem with this method is if there is a zero that should be there it
will be suppressed

Option 2: The formula on your sheet you copied to will look something
like this =Sheet1!A1, you could cahnge the formulas to this
=IF(Sheet1!A2="","",Sheet1!A2), you then do not need to do Option 1
and any real value zeros will still appear, you can then copy the
formula for other cells you want copied across


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=480045

Excel inserted 0's in cells linked to blank cells
 

Try 'Tools' 'Options' , on the 'View' tab, uncheck 'Zero Values' OK
Regards,
Alan.
"lburg801" wrote in
message ...

I added a work sheet by copying selected data from main worksheet (copy,
paste special, link) and where there were blank cells in original
worksheet, Excel inserted 0's. Is there a quick way to get rid of them.
Blank cells are to be expected in this database of names, info, etc.

My approach was to turn on auto filter and select for 0, highlight
column and delete contents. Hoping there is a less tedious way to do
this.

Thanks,
Trudy


--
lburg801
------------------------------------------------------------------------
lburg801's Profile:
http://www.excelforum.com/member.php...o&userid=28338
View this thread: http://www.excelforum.com/showthread...hreadid=480045

Excel inserted 0's in cells linked to blank cells
 

Paul Sheppard Wrote:
Hi Trudy

2 Options:

Option 1: Tools Options View Tab Window Options, click in the
zero values box to remove the tick, this will suppress all zeros'
problem with this method is if there is a zero that should be there it
will be suppressed

Option 2: The formula on your sheet you copied to will look something
like this =Sheet1!A1, you could cahnge the formulas to this
=IF(Sheet1!A2="","",Sheet1!A2), you then do not need to do Option 1
and any real value zeros will still appear, you can then copy the
formula for other cells you want copied across

I appreciate having two options. Surely one will work!

I have a question about Option 1. There are no fields on this worksheet
where a single zero would be input. Is it the occurrance of a single
zero that the option addesses, and not cells containing phone numbers
and addresses that might include zeros?

Thanks,
Trudy :)


--
lburg801
------------------------------------------------------------------------
lburg801's Profile: http://www.excelforum.com/member.php...o&userid=28338
View this thread: http://www.excelforum.com/showthread...hreadid=480045

Excel inserted 0's in cells linked to blank cells
 

lburg801 Wrote:
I appreciate having two options. Surely one will work!

I have a question about Option 1. There are no fields on this worksheet
where a single zero would be input. Is it the occurrance of a single
zero that the option addesses, and not cells containing phone numbers
and addresses that might include zeros?

Thanks,
Trudy :)

Hi Trudy


Option 1 will only supress a single zero, so zero's contained with
other data are safe


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=480045

Excel inserted 0's in cells linked to blank cells
 

Thanks so much!

Trudy :)


--
lburg801
------------------------------------------------------------------------
lburg801's Profile: http://www.excelforum.com/member.php...o&userid=28338
View this thread: http://www.excelforum.com/showthread...hreadid=480045