Cell format problem after copying

[jsullg]

I am trying to make a spreadsheet (a calendar) which is a composite of
two other spreadsheets (also calendars). I want the cells in the
composite to change when the two other original spreadsheets are
changed.

I have three sheets in my workbook--one for each of the two original
spreadsheets and one for the composite. I made the composite using
Copy then Paste Special function with the radio button on for "All" and
then clicking on the paste link button. But in the composite sheet any
cell that is a blank in either of the original sheets shows up as a 0
rather than blank. How can I get the composite to also be an empty
cell initially, but will change if the empty cell in the original gets
some data at a later time?

I'm assuming this is some sort of formatting problem, but I sure can't
figure it out. Can anyone help? Thank you........jsullg [/FONT]

 

[Mallycat]

Two options

Go to Tools\Options\View and uncheck Zero Values. The down side of
this is any genuine 0 you have will show as blank. If this is not a
problem to you then do this.

Second, you will have formulae in these cells with 0 something like

='sheet'!A2

Change these formula to =if('sheet'!A2=0,"","sheet'!A2)

Matt

 

[jsullg]

Matt,
Thank you for your reply. The first solution worked and that would
probably be fine for a calendar. In the interest of learning the best
solution I tried option number 2 and couldn't get it to work. Could
you explain a bit more? My two original sheets are called SGM and Tech
and my third sheet--the composite--is #1 COMP. I'm assuming for
solution #2 that the zero values box remains checked.

Thanks for your help.......Joan

 

[Mallycat]

Could you explain a bit more?

OK, instead of copying, then doing a paste special, go to clean blank
sheet in your workbook, click in cell A1 and then press = then click on
the tab for the first sheet, select call A1 and press enter. This gives
you cell A1 in the second sheet pointing to A1 in the first sheet. Edit
this formula in the second sheet so that IF the answer is blank, then
put in a blank.

ie =if(sheet1A1="","",sheet1A1)

Then copy the formula to the rest of the second spreadsheet. Over
shoot the width and length if you like, then go back and delete the
ones you don't need.

Matt

 

[babycody]

You could use a custom format for those cells of General;-General;
Format>Cells then select the number tab. On the left you will see
custom. Select it, and enter General;-General; in the box to the right.
Now when you use zeros they won't show up, but all other numbers will.