how do i copy/paste formulas between work BOOKS?

[okigram]

hi,

the problem i am having is that whenn i try to paste formulas between work books created in different excel sessions, it just pastes the value. if i do a 'paste special' my options are to paste images or a link object...none of which i want to do...i want it to paste the actual formula but it won't do it, it will only allow me to paste formulas between sheets in the same workbook or between workbooks created from the same original workbook. what am i doing wrong?
thanks

 

[Volk359]

Can you try to copy the formula itself rather than the cell?

 

[GlennUK]

Why can't you open the workbooks in the same Excel session? Psting will work then.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[lkerr]

I have had this problem in the past, as well, usually when working in 97 - I think it might be a bug. Here's my workaround = I copy the formula, then paste it into word. I open the second workbook, then paste it into the new cell. Somehow this works when all else fails. Go figure.

 

[amarmstrong]

As far as I know, so long as you have both workbooks open at the same time, you should be able to copy--> paste the formulas between them w/o any paste special. the only time it may cause a problem is if you have absolute references within the formula; or relative cell references in the destination workbook make things whonky. I basically tried this with a couple of formulas

2 workbooks... test1 & test2 are open

copy (via CTRL + C) formula from test1
switch to test2 (via CTRL+TAB)
clicked in destination cell
paste (via CTRL+V) into test2

needless to say, i'm a keyboard shortcut junky but it seemed to work fine.

 

[okigram]

thanks for all the replies.

I tried to open the workbooks i nthe same session. it does allow me to paste and the formulas come through. however they still reference the old spreadsheet. how do i get it to refrain from referencing the other exxel file. i would rather not have to go into all of the formulas and edit them.

the other problem is that i am pasting the whole sheet because there are about 10 formulas in it.

thanks again

 

[GlennUK]

Hi okigram,

can you give examples of the formulas in the original book, and also the formulas after you've done your paste.

Can you explain your other problem:

the other problem is that i am pasting the whole sheet because there are about 10 formulas in it.

... and what exactly is the problem here?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[okigram]

there are 10 variations on the below formula in sheet2.


=IF(ISERROR(MATCH(IF(ISTEXT(IdEntered),IdEntered,TEXT(IdEntered,"0")),PS_ID,0)),"",INDEX(Employee2,MATCH(IF(ISTEXT(IdEntered),IdEntered,TEXT(IdEntered,"0")),PS_ID,0),28))

PS_ID is the named range for column 1 in sheet1
IdEntered is the named range for cell b2 in sheet2
Employee2 is the named range for all cells in sheet1

i want to past sheet2 into another workbook (as i receive workbooks via email from the plants. the problem i am having is that when i paste the sheet it won't paste the formulas without linking it to the workbook from where it's been pasted or it wants to put an object in there which references the old workbook. all i want to do is past the formulas and not have to edit out the links.

hope i'm clear here, thanks.

 

[GlennUK]

So, I assume you have a Sheet1 in the new workbook. And you must have the same defined names in that sheet as in the original book, otherwise redirecting the links won't work. Once you've pasted your formulae, go into menu command Edit/Links and highlight the link that is causing trouble ( for example, pointing to the old workbook, Sheet1 ), and click the Change Source button, and select the new workbook.

Let us know if that works.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[okigram]

thanks, that seems to do the trick. is there any way to automate this? i only ask because i have HR people that will be copying/pasting this worksheet into the workbooks as they receive them from the plants. i would like to limit explanation and potential mistakes as much as possible!!

 

[GlennUK]

There's no way to automate your current process without using VBA macro code.

Have you thought about changing your process ... for example, having a template workbook with sheet1 and sheet2 ( with all those formulas ), and instead copy and paste the information into sheet1 of that workbook.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[okigram]

yes, but this is just a 'quick fix'(famous last words) that 'management' wanted. they've already sent out the spreadsheets to the mfg plants. this will suffice. thanks for your help Glenn!

 

[GlennUK]

my pleasure

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.