Another Excel question

[gismo]

Is there any way to copy formulas from cells from workbook to workbook without Excel adding workbook (file) references? Say I have a cell in book1.xls that's the product of the value in two other cells in that same workbook and I want to make a separate but identical copy of all that in book2. If I cut and paste the cell with the product formula refers to the multiplacands in book1 rather than the copies in book2. I looked at all the options in the "paste special" menu and couldn't find anything useful there. So far the only way I found to do this is to edit and copy the text of the formula and paste that. Unfortunately this way I can only copy one formula cell at a time and of course none of the formatting gets copied at the same time.

 

[Areeda]

Lance,

Try Paste Special from the Edit menu, choose the formula radio button.

Joe

 

[gismo]

Lance,

Try Paste Special from the Edit menu, choose the formula radio button.

Joe

Tried that, it puts in the full workbook reference and leaves the format unchanged.

BTW I just checked and the problem I'm having only occurs when copying from one open workbook to another, not sheet to sheet in the same workbook. I'll edit the original post to indicate this.

 

[flyingcheesehead]

Lance,

I just tried this and it worked as you want with just copy & paste. I had to click "Paste Link" in the Paste Special dialog to get the file reference to appear. Of course, I'm on a Mac, but the M$ Office apps are usually darn close across platforms (even using all the &**%# annoying Windows key combinations). Note also that the formulas I tested this on are mostly conditionals, not multiplication (don't know what difference that would make).

Maybe something in the preferences?

OK, played with it a bit more. When I copy/paste formulas with a reference to another sheet, it includes a file reference.

 

[The Old Man]

Try this: (I haven't, but it seems logical)

Copy the entire sheet to the other workbook.
Copy the desired formulae
Delete the copied worksheet.

If the desired formulae do not reference other worksheets, it should work.

 

[gismo]

Try this: (I haven't, but it seems logical)

Copy the entire sheet to the other workbook.
Copy the desired formulae
Delete the copied worksheet.

If the desired formulae do not reference other worksheets, it should work.

It works as you state, unfortunately I need to copy formulae with sheet to sheet references. What I'm really trying to do is make multiple copies of a workbook including several sheets with differences in values and some formulas, then copy selected changes from one workbook to the other. An alternative I'm considering is to segregate the variable data and formulae from the fixed stuff and putting all the fixed stuff in a separate book, but right now that looks to be a significant task and there's always the chance that part of what is "fixed" today may be variable tomorrow and vice versa.

 

[Len Lanetti]

Lance,

Can you make a copy of the entire file then open the file in Excel and modify to copy as required.

Len

 

[gismo]

Lance,

Can you make a copy of the entire file then open the file in Excel and modify to copy as required.

Len

Yes I could, but that won't accomplish my goal. I already have copied the file and I want to subsequently copy corrections made to one copy into another. I can't re-copy the file because some of the changes made to one copy need to remain different from the original.

What I really need is an option in "Paste Special" that copies formulas, formatting, and values but sheds the workbook portion of any references (or any alternative that accomplishes the same thing).

 

[The Old Man]

Can you do a search and replace the "book1:" references with ""?

 

[mikea]

Can you do a search and replace the "book1:" references with ""?

That might work.

You can definitely do that with macro code.