Zero replaces blank field when paste special is used
 

I need to bring numeric values forward from ten separate spreadsheets (survey
results) to a "helper spreadsheet" in order to calculate the mode of these
values for each survey question. I have data from two surveys and the values
are brought forward with no problems when I use Paste Special. However, when
I attempt to copy and past special the cells for the remaining eight survey
spreadsheets (for which no data is currently present) the result that is
copied to the "helper spreadsheet" is a zero rather than a blank field. The
zero isthen identified as a value and all my summary data (brought forward to
a separate "Summary" worksheet) indicates zero as the mode for each of the
survey questions. I appreciate any help you can offer.

Best regards,

Greg

Zero replaces blank field when paste special is used
 

I understand you to say that you copy cells that have nothing in them
and do a Edit|Paste Special|Values and get zeroes corresponding to those
empty cells.

If this is correct, then please specify the version of Excel you are
using (I have not seen this behavior in any version of Excel that I have
used). If my restatement is not correct, then please describe more
completely what your situation is.

Note that no cell containing a formula is empty. If instead of copying
the original empty cells, you have formula in your summary sheet that
points to the original location, then that formula will return zero
where the original cell is empty.

You might also check that in Tools|Options|View, Zero values is checked,
so that you will see actual zeros that there.

Jerry

Baseball Greg wrote:

I need to bring numeric values forward from ten separate spreadsheets (survey
results) to a "helper spreadsheet" in order to calculate the mode of these
values for each survey question. I have data from two surveys and the values
are brought forward with no problems when I use Paste Special. However, when
I attempt to copy and past special the cells for the remaining eight survey
spreadsheets (for which no data is currently present) the result that is
copied to the "helper spreadsheet" is a zero rather than a blank field. The
zero isthen identified as a value and all my summary data (brought forward to
a separate "Summary" worksheet) indicates zero as the mode for each of the
survey questions. I appreciate any help you can offer.

Best regards,

Greg

Zero replaces blank field when paste special is used
 

Jerry:

Thanks for your message. I am using Excell 2003.

Because I am bringing the values forward from one spreadsheet to another and
need them to update as data is entered I am doing an Edit|Paste Special|All
so that the Paste Link button is active.

I have checked the cells in the eight surveys worksheets (that do not
contain data) and the fields are indeed empty. The fields in the "helper
workseet" contain only the reference back to the survey worksheet, labled as
='Course C'!D4 for example.

I inserted an entirely new worksheet and performed an Edit|Paste Special|All
bringing it forward to an empty cell on the "helper worksheet" and got the
same zero as previously reported.

Am I correct in my assumption that a cell is empty if after selected,
nothing appears in the function bar (probably not the correct term--the cell
following the function sign fx) at the top of the spreadsheet.

I have tried the Tools|Options|View with the Zeros values box both checked
and unchecked, but there is not difference in the output to the "helper
worksheet" and resulting Summary sheet.

Appreciate your interest in my problem.

Greg



"Jerry W. Lewis" wrote:

I understand you to say that you copy cells that have nothing in them
and do a Edit|Paste Special|Values and get zeroes corresponding to those
empty cells.

If this is correct, then please specify the version of Excel you are
using (I have not seen this behavior in any version of Excel that I have
used). If my restatement is not correct, then please describe more
completely what your situation is.

Note that no cell containing a formula is empty. If instead of copying
the original empty cells, you have formula in your summary sheet that
points to the original location, then that formula will return zero
where the original cell is empty.

You might also check that in Tools|Options|View, Zero values is checked,
so that you will see actual zeros that there.

Jerry

Baseball Greg wrote:

I need to bring numeric values forward from ten separate spreadsheets (survey
results) to a "helper spreadsheet" in order to calculate the mode of these
values for each survey question. I have data from two surveys and the values
are brought forward with no problems when I use Paste Special. However, when
I attempt to copy and past special the cells for the remaining eight survey
spreadsheets (for which no data is currently present) the result that is
copied to the "helper spreadsheet" is a zero rather than a blank field. The
zero isthen identified as a value and all my summary data (brought forward to
a separate "Summary" worksheet) indicates zero as the mode for each of the
survey questions. I appreciate any help you can offer.

Best regards,

Greg

Zero replaces blank field when paste special is used
 

Paste Link creates formulas that point back to the original worksheet.
The formula ='Course C'!D4 must return something. Excel has no true
missing value, so if 'Course C'!D4 is empty, then ='Course C'!D4
will return a zero. This is the way that formulas work, and has nothing
to do with whether the formula was created by pasting or not.

You can edit the formula to say
=IF(ISBLANK('Course C'!D4),"",'Course C'!D4)

Jerry

Baseball Greg wrote:

Jerry:

Thanks for your message. I am using Excell 2003.

Because I am bringing the values forward from one spreadsheet to another and
need them to update as data is entered I am doing an Edit|Paste Special|All
so that the Paste Link button is active.

I have checked the cells in the eight surveys worksheets (that do not
contain data) and the fields are indeed empty. The fields in the "helper
workseet" contain only the reference back to the survey worksheet, labled as
='Course C'!D4 for example.

I inserted an entirely new worksheet and performed an Edit|Paste Special|All
bringing it forward to an empty cell on the "helper worksheet" and got the
same zero as previously reported.

Am I correct in my assumption that a cell is empty if after selected,
nothing appears in the function bar (probably not the correct term--the cell
following the function sign fx) at the top of the spreadsheet.

I have tried the Tools|Options|View with the Zeros values box both checked
and unchecked, but there is not difference in the output to the "helper
worksheet" and resulting Summary sheet.

Appreciate your interest in my problem.

Greg



"Jerry W. Lewis" wrote:


I understand you to say that you copy cells that have nothing in them
and do a Edit|Paste Special|Values and get zeroes corresponding to those
empty cells.

If this is correct, then please specify the version of Excel you are
using (I have not seen this behavior in any version of Excel that I have
used). If my restatement is not correct, then please describe more
completely what your situation is.

Note that no cell containing a formula is empty. If instead of copying
the original empty cells, you have formula in your summary sheet that
points to the original location, then that formula will return zero
where the original cell is empty.

You might also check that in Tools|Options|View, Zero values is checked,
so that you will see actual zeros that there.

Jerry

Baseball Greg wrote:


I need to bring numeric values forward from ten separate spreadsheets (survey
results) to a "helper spreadsheet" in order to calculate the mode of these
values for each survey question. I have data from two surveys and the values
are brought forward with no problems when I use Paste Special. However, when
I attempt to copy and past special the cells for the remaining eight survey
spreadsheets (for which no data is currently present) the result that is
copied to the "helper spreadsheet" is a zero rather than a blank field. The
zero isthen identified as a value and all my summary data (brought forward to
a separate "Summary" worksheet) indicates zero as the mode for each of the
survey questions. I appreciate any help you can offer.

Best regards,

Greg

Zero replaces blank field when paste special is used
 

Jerry:

Excellent! Typing the new formula into the "helper spreadsheet" cells
brought my mode numbers forward to my "Summary" sheet perfectly! Thanks for
sticking with my problem and the great assistance you provided. Happy
holidays.

Greg

"Jerry W. Lewis" wrote:

Paste Link creates formulas that point back to the original worksheet.
The formula ='Course C'!D4 must return something. Excel has no true
missing value, so if 'Course C'!D4 is empty, then ='Course C'!D4
will return a zero. This is the way that formulas work, and has nothing
to do with whether the formula was created by pasting or not.

You can edit the formula to say
=IF(ISBLANK('Course C'!D4),"",'Course C'!D4)

Jerry

Baseball Greg wrote:

Jerry:

Thanks for your message. I am using Excell 2003.

Because I am bringing the values forward from one spreadsheet to another and
need them to update as data is entered I am doing an Edit|Paste Special|All
so that the Paste Link button is active.

I have checked the cells in the eight surveys worksheets (that do not
contain data) and the fields are indeed empty. The fields in the "helper
workseet" contain only the reference back to the survey worksheet, labled as
='Course C'!D4 for example.

I inserted an entirely new worksheet and performed an Edit|Paste Special|All
bringing it forward to an empty cell on the "helper worksheet" and got the
same zero as previously reported.

Am I correct in my assumption that a cell is empty if after selected,
nothing appears in the function bar (probably not the correct term--the cell
following the function sign fx) at the top of the spreadsheet.

I have tried the Tools|Options|View with the Zeros values box both checked
and unchecked, but there is not difference in the output to the "helper
worksheet" and resulting Summary sheet.

Appreciate your interest in my problem.

Greg



"Jerry W. Lewis" wrote:


I understand you to say that you copy cells that have nothing in them
and do a Edit|Paste Special|Values and get zeroes corresponding to those
empty cells.

If this is correct, then please specify the version of Excel you are
using (I have not seen this behavior in any version of Excel that I have
used). If my restatement is not correct, then please describe more
completely what your situation is.

Note that no cell containing a formula is empty. If instead of copying
the original empty cells, you have formula in your summary sheet that
points to the original location, then that formula will return zero
where the original cell is empty.

You might also check that in Tools|Options|View, Zero values is checked,
so that you will see actual zeros that there.

Jerry

Baseball Greg wrote:


I need to bring numeric values forward from ten separate spreadsheets (survey
results) to a "helper spreadsheet" in order to calculate the mode of these
values for each survey question. I have data from two surveys and the values
are brought forward with no problems when I use Paste Special. However, when
I attempt to copy and past special the cells for the remaining eight survey
spreadsheets (for which no data is currently present) the result that is
copied to the "helper spreadsheet" is a zero rather than a blank field. The
zero isthen identified as a value and all my summary data (brought forward to
a separate "Summary" worksheet) indicates zero as the mode for each of the
survey questions. I appreciate any help you can offer.

Best regards,

Greg

Zero replaces blank field when paste special is used
 

You're welcome; glad it helped.

Jerry

Baseball Greg wrote:

Jerry:

Excellent! Typing the new formula into the "helper spreadsheet" cells
brought my mode numbers forward to my "Summary" sheet perfectly! Thanks for
sticking with my problem and the great assistance you provided. Happy
holidays.

Greg