• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to copy a formula from one spreadsheet to the other.

Eloise T

Eloise T

Active Member
When copying from one spreadsheet to another, sometimes I get a Paste Special window that contains "Formulas," "Values," and others options, and at other times I get "Microsoft Excel 2003 Worksheet Object" and et. al. in a completely different Paste Special window. What causes this?
 
Last edited:
Hi Eloise.
Checking terminology, it sounds like you have two different workbooks that you are copying between?

If they're in the same instance of XL, you should be able to just do a Paste Special - Formulas.
upload_2016-1-27_11-16-38.png

If they're not in the same instance, you're paste special options will look much different, like this:
upload_2016-1-27_11-17-21.png

If that's the case, you can't copy formulas. If you're seeing the latter, close one of the files completely, go to the other, and use the File - Open dialogue to open the file you just closed, but this time it'll be in the same instance.
 
Hi Eloise,

Follow the below steps, Copy the column with the keyword ctl+c and go to the location where you want to dumb the data with the formula press atl+e+s and select formula.

Hope this is helpful

Regards,
JD
 
Luke M said:
Hi Eloise.
Checking terminology, it sounds like you have two different workbooks that you are copying between?

If they're in the same instance of XL, you should be able to just do a Paste Special - Formulas.
View attachment 26564

If they're not in the same instance, you're paste special options will look much different, like this:
View attachment 26565

If that's the case, you can't copy formulas. If you're seeing the latter, close one of the files completely, go to the other, and use the File - Open dialogue to open the file you just closed, but this time it'll be in the same instance.
Click to expand...
You nailed it! Those are the windows I'm seeing. Can you define "instance?"
I was trying to copy a small area (~10x10 cells) from an existing spreadsheet and paste it to a brand new spreadsheet to forward to someone. Would "instance" be the spreadsheets are either using the same "window/icon" at the bottom of the page vs. each spreadsheet having their own separate appearance?

I don't understand why Excel works that way but I think I understand now. Thanks.
 
Last edited:
Luke M said:
Hi Eloise.
Checking terminology, it sounds like you have two different workbooks that you are copying between?

If they're in the same instance of XL, you should be able to just do a Paste Special - Formulas.
View attachment 26564

If they're not in the same instance, you're paste special options will look much different, like this:
View attachment 26565

If that's the case, you can't copy formulas. If you're seeing the latter, close one of the files completely, go to the other, and use the File - Open dialogue to open the file you just closed, but this time it'll be in the same instance.
Click to expand...
By the way, what did you use to capture the Paste Special "windows" on Excel and paste them here?
 
I'll give it a shot. Appearance of icons/layout may vary depending on your system settings, so I'll try to explain via other route.

If you started with everything closed, and then opened XL you should have a blank workbook visible. If you close just the workbook, but not XL, you'll see a gray space. The application though, is still open. This is the XL application, or instance. Within this instance, you can have one or many workbooks open. So, the instance is like the box holding all the open workbooks.

However, you can run another application of XL, which would create another instance (box). This box would have it's own set of workbooks.

Why this matters: As you've found, within an instance, XL can do much more in terms of copying and building formulas. It knows everything there is to know about the workbooks within it's box. If it tries to go to another instance, it's like grabbing data from a web page...it can see the visible text, but it can no longer see the full formatting or formulas.

How to check: The one way to check instances in all versions of XL seems to be the Windows menu. If you go to View - Windows - Switch Windows, you can see all the workbooks open in the instance of XL. If you don't see the workbook you want, it's in a different instance.

For the Screen shots, I tend to use the windows snipping capture tool (From start menu, search for 'Snip'. You can take a screenshot and crop, or any picture/shape and paste directly into the response box.
 
Luke M said:
I'll give it a shot. Appearance of icons/layout may vary depending on your system settings, so I'll try to explain via other route.

If you started with everything closed, and then opened XL you should have a blank workbook visible. If you close just the workbook, but not XL, you'll see a gray space. The application though, is still open. This is the XL application, or instance. Within this instance, you can have one or many workbooks open. So, the instance is like the box holding all the open workbooks.

However, you can run another application of XL, which would create another instance (box). This box would have it's own set of workbooks. Makes sense!

Why this matters: As you've found, within an instance, XL can do much more in terms of copying and building formulas. It knows everything there is to know about the workbooks within it's box. If it tries to go to another instance, it's like grabbing data from a web page...it can see the visible text, but it can no longer see the full formatting or formulas.

How to check: The one way to check instances in all versions of XL seems to be the Windows menu. If you go to View - Windows - Switch Windows, you can see all the workbooks open in the instance of XL. If you don't see the workbook you want, it's in a different instance. Great information!

For the Screen shots, I tend to use the windows snipping capture tool (From start menu, search for 'Snip'. You can take a screenshot and crop, or any picture/shape and paste directly into the response box.
Click to expand...
Good to know! (See above.)
 
upload_2016-1-27_13-12-40.png This works with new Posts, but I was unable to Snip and paste while editing an existing Post above. "Snip" is very useful! Thank for sharing.
 
In addition to what bobhc said, when you edit, you'll need to hit this button in bottom right:
upload_2016-1-27_15-42-53.png

if you want to paste images, or upload a file.
 
You must log in or register to reply here.
Back
Top