how can I paste values derived from formulas into a different book

[Guest]

I am trying to copy values from one workbook (apps 2006-07) into a new
version of the same workbook (apps 2007-08) but the paste special option only
creates a link to the original formula in the first workbook, e.g. =(('[APPS
2006-07.xls]season apps'!C4))+42.

I want to add the values created in the original workbook to the same
formula in the new book to produce a new list of values. The values are
calculated in the original workbook by a simple SUM formula: =('season
apps'!C4)+32 which adds the value from one sheet to a constant value. I want
to do the same in my new workbook without this being linked to the original
workbook. I ams ure I ahve done this in the past but Excel doesn't seem to
want to play this year!

 

[Susan]

if you want just the values, paste special, values.
but it sounds like you want the formula, just linked to the new wrkbk
instead of the old one.
try this:
paste it the way you are.
press Ctrl plus " ` " - this is the character that's (usually)
just above the tab key on your keyboard. it's a backwards apostrophe.
when you hit this, every formula in the worksheet will open up & be
readable. it will screw up your formatting & column widths
drastically, but don't worry, they'll go back.
now you can edit, find & replace IN THE FORMULAS.

replace: [APPS2006-07.xls]
with: <blank>
all your formulas will now say simply
'season apps'!

now hit Ctrl + ` again
and all your formulas will close up again, all your formatting will
return to normal, & you're all set.
hope it works!

susan

 

[Guest]

Dear Susan,

Many thanks; it isn't a way I have used in the past but that doesn't matter
- it works! You have saved me many frustrating hours at the computer trying
to remember what I did last time and trying to work out why it isn't doing it
this time!

Martin (deepdale1881)

if you want just the values, paste special, values.
but it sounds like you want the formula, just linked to the new wrkbk
instead of the old one.
try this:
paste it the way you are.
press Ctrl plus " ` " - this is the character that's (usually)
just above the tab key on your keyboard. it's a backwards apostrophe.
when you hit this, every formula in the worksheet will open up & be
readable. it will screw up your formatting & column widths
drastically, but don't worry, they'll go back.
now you can edit, find & replace IN THE FORMULAS.

replace: [APPS2006-07.xls]
with: <blank>
all your formulas will now say simply
'season apps'!

now hit Ctrl + ` again
and all your formulas will close up again, all your formatting will
return to normal, & you're all set.
hope it works!

susan

I am trying to copy values from one workbook (apps 2006-07) into a new
version of the same workbook (apps 2007-08) but the paste special option only
creates a link to the original formula in the first workbook, e.g. =(('[APPS
2006-07.xls]season apps'!C4))+42.

I want to add the values created in the original workbook to the same
formula in the new book to produce a new list of values. The values are
calculated in the original workbook by a simple SUM formula: =('season
apps'!C4)+32 which adds the value from one sheet to a constant value. I want
to do the same in my new workbook without this being linked to the original
workbook. I ams ure I ahve done this in the past but Excel doesn't seem to
want to play this year!

 

[Susan]

i'm glad it worked for you!
thanks for the feedback.
susan

Dear Susan,

Many thanks; it isn't a way I have used in the past but that doesn't matter
- it works! You have saved me many frustrating hours at the computer trying
to remember what I did last time and trying to work out why it isn't doing it
this time!

Martin (deepdale1881)

if you want just the values, paste special, values.
but it sounds like you want the formula, just linked to the new wrkbk
instead of the old one.
try this:
paste it the way you are.
press Ctrl plus " ` " - this is the character that's (usually)
just above the tab key on your keyboard. it's a backwards apostrophe.
when you hit this, every formula in the worksheet will open up & be
readable. it will screw up your formatting & column widths
drastically, but don't worry, they'll go back.
now you can edit, find & replace IN THE FORMULAS.

replace: [APPS2006-07.xls]
with: <blank>
all your formulas will now say simply
'season apps'!

now hit Ctrl + ` again
and all your formulas will close up again, all your formatting will
return to normal, & you're all set.
hope it works!

susan

I am trying to copy values from one workbook (apps 2006-07) into a new
version of the same workbook (apps 2007-08) but the paste special option only
creates a link to the original formula in the first workbook, e.g. =(('[APPS
2006-07.xls]season apps'!C4))+42.
I want to add the values created in the original workbook to the same
formula in the new book to produce a new list of values. The values are
calculated in the original workbook by a simple SUM formula: =('season
apps'!C4)+32 which adds the value from one sheet to a constant value. I want
to do the same in my new workbook without this being linked to the original
workbook. I ams ure I ahve done this in the past but Excel doesn't seem to
want to play this year!- Hide quoted text -

- Show quoted text -