Problem in copying cells 1

[SheepDog]

The workbook you opened contains automatic links to information in another workbook. Do you want to update this workbook with changes made to the other workbook. I am trying to copy cells from one workbook and paste them into another workbook. I get this error when I open the sheet in the workbook that I am pasting into.

I have done this before without a problem.

 

[macropod]

Hi Sheepdog,

Whenever you copy cells that contain formulae referring to other worksheets/workbooks into a new workbooks, the formulae will continue to reference the same source as the originals, even if the source data are copied over. The same happens if you cut and paste without cutting & pasting source cells referenced in formulae on the same sheet.

When copying & pasting, you could avoid this by copying over the cell contents individually, which could be a pain, or you can correct it afterwards by doing a search & replace to delete the newly-added references to the original workbook.

If cutting & pasting, ensuring that both the formulae and their source cells are cut & paste into the new workbook will also avoid this problem.

Cheers

 

[BerylM]

Or you could use Edit, Paste Special, Values to paste just the results of the formulae without the formulae themselves ...?

HTH
BerylM

 

[SheepDog]

Is there a way to copy a sheet from one spreadsheet to another? If so am I going to run into the same problem as copying cells and pasting?

 

[KenWright]

Or depending on the version of Excel you are running with, just copy and apste as you are doing, then use Edit / Links / Change Source and then point the links back to the current workbook, which will effectively break them but retain your formulas. If you don't care about formulas you can also just do Edit / Break Links and it will hardcode all the data.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[BerylM]

Easiest way to move an entire sheet to another workbook - display the two workbooks so you can see both and drag the tab of the sheet you want over to the other workbook. Hold down Ctrl while you do it if you want a copy (rather than moving it entirely).

 

[dcompto]

Ken, your Edit / Links / Change Source explanation is very helpful to me. Thanks, and have a star.

 

[SheepDog]

I used this method:

Edit / Links / Change Source and then point the links back to the current workbook. What happened in two of the columns for just one year was this. I had #DIV/0! and #VALUE
everything else was okay. Why did this happen?

 

[SheepDog]

Let's say I want to type in formaulas rather than copy ells or sheets.
If I type in =IF(H259=",",(H259-B259)/B259)
how to I get the number to show in the cell rather than the formula?

 

[KenWright]

Sheepdog -

Post 1
Likely as not there are cells in the formulas that in the previous sheet referred to values in the cells they refer to, but you simply haven't copied those values across, and so they now point to empty cells.

Post2
If the cell is formatted as text then you will see just the formula. Format as any numeric format, or even general and then reneter the formula or just hit F2 and enter. Note, your IF statement has no 'If False' argument, is that intentional?

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------