Copying Excel Spreadsheet data

[rwguinn]

We have to do a bunch of analysis in Excel (not my choice!)
When we copy a sheet into a report, which we need to do because of the formulas, pictures, and text-not wanting to re-do the whole damn thing--it shows up with that damn #VALUE! crap when you open the copied version.
This in spite of the fact the macro is present, and the "do not update" option is clicked.
When the original spread sheet is also opened, this does not occur. If you open the original, then close it, the problem goes away, also.
We need the whole thing, as the people I'm dealing with want to see how cells were calculated, and the references within the calculations. They are also big on pictures.
We are using the "move or copy" option, with "make a copy" into the new worksheet.
Any guesses or actual facts on whatthehecck I'm doing wrong?

 

[biomorph]

We have to do a bunch of analysis in Excel (not my choice!)
When we copy a sheet into a report, which we need to do because of the formulas, pictures, and text-not wanting to re-do the whole damn thing--it shows up with that damn #VALUE! crap when you open the copied version.
This in spite of the fact the macro is present, and the "do not update" option is clicked.
When the original spread sheet is also opened, this does not occur. If you open the original, then close it, the problem goes away, also.

From what you've said in the last paragraph here it sounds like the new sheet is still relying on the original sheet being open, and when you haven't got that happening it can't find the data.

Also I'm not too sure what sort of report you are trying to generate, is it another spreadsheet? It sounds like it.

If the original sheet is open the problem would go away if this is the case, and if it has been open the data is probably in the ram of the pc, so it still would not give the error.

Also the new sheet might not have the cell formats correctly set (number, text, etc) the same as the original sheet. I think this would also show the same error (I think!) if the new sheet is set to "text" for example in the relevant cells should those cells contain formulae or numbers....
You get the same error if you are trying to add up text, instead of numbers too.

We need the whole thing, as the people I'm dealing with want to see how cells were calculated, and the references within the calculations. They are also big on pictures.
We are using the "move or copy" option, with "make a copy" into the new worksheet.

excell will link to the old spreadsheet by default if you do that as described above, most likely.

look at the formulae in the copy and see if they refer to the other sheet, if so delete that part of the formula that does the referencing bit.

Check the cells you are pasting into are not set to "text" or some format that will not accept the math formular. It won't add up text or pictures, and gives you the value error instead, likewise if the link it is trying to follow doesn't exist or isn't open sometimes.
Also try http://www.updatexp.com/excel-value-error.htmlthis.

If non of the above is any use, give us a bit more detail.

Any guesses or actual facts on whatthehecck I'm doing wrong?

I'm guessing a little here, ok?

also the "Do not update" option is not relevant and only applies if the data in the first sheet has been changed, if it hasn't then turning it on or off will do nothing...

regards

 

[biomorph]

duplicate post

(database error)

 

[ZirconBlue]

Check under Tools>Options, on the Calculation tab, to see if "Save external link values" and "Update remote references" are checked. (I have an older version of Excel, so this may not be the exact wording in your version.)

 

[cwalner]

When the original spread sheet is also opened, this does not occur. If you open the original, then close it, the problem goes away, also.

This definitely means that you have external links to the original document in the copy.

If the data it is supposed to be linked to has also been copied to the new workbook, in the same worksheet name and cells as the original then the best solution is to Edit Links > Change Source, then when the box to navigate to the new source document opens up, select the same document you are in (the new one) thus linking the formulas back to itself.

 

[rwguinn]

From what you've said in the last paragraph here it sounds like the new sheet is still relying on the original sheet being open, and when you haven't got that happening it can't find the data.

Also I'm not too sure what sort of report you are trying to generate, is it another spreadsheet? It sounds like it.

If the original sheet is open the problem would go away if this is the case, and if it has been open the data is probably in the ram of the pc, so it still would not give the error.

Also the new sheet might not have the cell formats correctly set (number, text, etc) the same as the original sheet. I think this would also show the same error (I think!) if the new sheet is set to "text" for example in the relevant cells should those cells contain formulae or numbers....
You get the same error if you are trying to add up text, instead of numbers too.


excell will link to the old spreadsheet by default if you do that as described above, most likely.

look at the formulae in the copy and see if they refer to the other sheet, if so delete that part of the formula that does the referencing bit.

Check the cells you are pasting into are not set to "text" or some format that will not accept the math formular. It won't add up text or pictures, and gives you the value error instead, likewise if the link it is trying to follow doesn't exist or isn't open sometimes.
Also try http://www.updatexp.com/excel-value-error.htmlthis.

If non of the above is any use, give us a bit more detail.


I'm guessing a little here, ok?

also the "Do not update" option is not relevant and only applies if the data in the first sheet has been changed, if it hasn't then turning it on or off will do nothing...

regards

yeah, it's excel-to-excell. The report is a summary of a whole *pot full of spreadsheets.
It's mostly an exercise in futility, since they want to look at a cell and find where it came from, and trace it back to the original raw data--sometimes as many as 2 workbooks ago! When we move it into a place they can get at it all the links end up being broken, anyway.
Breaking the links in "edit" works, leaves the values and calculations intact, but traceability is gone (which, as I pointed out above, makes it moot anyway)
I think the best I can hope for is no #VALUE!# errors to show up in the final product. That's what I am shooting for, now.
Thanks, folks

 

[cwalner]

rwguinn,

sounds like you are posting this to a shared network drive. I am not certain how you define your links, but you may need to redo the links to remove references to mapped drives.

example. formula is linked to D:\reports\rwguinn\filename, where D: is your local mapping of
shared drive on 'acmecorpserver'

if you redefine the link to \\acmecorpserver\shared drive\reports\rwguinn\filename

you will not lose the links when others open the file but don't have the same mappings defined that you have.

 

[rwguinn]

rwguinn,

sounds like you are posting this to a shared network drive. I am not certain how you define your links, but you may need to redo the links to remove references to mapped drives.

example. formula is linked to D:\reports\rwguinn\filename, where D: is your local mapping of
shared drive on 'acmecorpserver'

if you redefine the link to \\acmecorpserver\shared drive\reports\rwguinn\filename

you will not lose the links when others open the file but don't have the same mappings defined that you have.

it is to laugh. IT from both companies, in their infinite wisdom, have declared that company A employess are not allowed to access company B servers, and viceversa, except for certain selected employees.
So no matter what I do to the links, they die when joe ITiot moves them to B's server.
ain't life grand?
I'd a whole lot rather be doing engineering, on the whole...

 

[cwalner]

it is to laugh. IT from both companies, in their infinite wisdom, have declared that company A employess are not allowed to access company B servers, and viceversa, except for certain selected employees.
So no matter what I do to the links, they die when joe ITiot moves them to B's server.
ain't life grand?
I'd a whole lot rather be doing engineering, on the whole...

Sorry,

but it seems as if Linux, the god of IT, has you on his computer screen with his finger poised over the SMITE button (tm - Gary Larsen)

 

[biomorph]

it is to laugh. IT from both companies, in their infinite wisdom, have declared that company A employess are not allowed to access company B servers, and viceversa, except for certain selected employees.
So no matter what I do to the links, they die when joe ITiot moves them to B's server.
ain't life grand?
I'd a whole lot rather be doing engineering, on the whole...

now that makes more sense, I've had a similar thing myself using clustered Samba servers on linux, and the answer is to have a properly thought out user/group policy that all the server admins understand.
.
sounds to me like the server admin guys need to get a grip/be told on who is allowed what sort of access and where..

This isn't hard to do with samba, though i'm a bit rusty on NT and other systems.

like I've said, your IT user/group policies need sorting out..............it isn't excel's problem I reckon..