Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy and paste special - values into new excel file | Excel Discussion (Misc queries) | |||
trying to "paste special" data; why does worksheet object appear? | Excel Discussion (Misc queries) | |||
excel paste special should allow text | Excel Worksheet Functions | |||
Paste Special | Excel Discussion (Misc queries) | |||
Paste Special Question | Excel Discussion (Misc queries) |