Copy formula from one Workbook to another workbook

[clovis]

Hello guys,

I'd like to know if it's possible to (force) copy formula's from one workbook to another.
At the moment only the result of a formula, link or format can be copied.
Some colleagues prefer working with 2 workbooks side by side (one on each screen) than 2 files arranged in one workbook spread over 2 screens out.
Thanks

[mikeTRON]

You can convert the formula to a string then paste it into the new workbook, assuming the two workbooks have the same structure.

[MrShorty]

I will note that I have never had any difficulty copying a formula from one workbook to another, and never been limited to pasting results, links, or formats. Can you describe in some detail what you are trying to do, so we can see if we can replicate what you are seeing?

[clovis]

I'm trying to copy formulas from one Excel(workbook)
p1.JPG
to another one
p3.JPG
but as you can see the options are limited to 3 choices.
Thanks for any help

[MrShorty]

In 2007, I get a different context menu in that situation. Amongst other options, the only "paste" options are "paste" which copies the formula and formats and everything (the usual paste command) or the paste special command. I am not limited in my paste options like your context menu shows.

I would be curious what options you have if you click on "paste special"? Is the "formulas" option grayed out?

Someone with 2010 or later may need to step in here and comment. I cannot imagine that this basic copy/paste behavior would have changed that dramatically between 2007 and 2010 such that one cannot copy/paste formulas from one workbook to another.

[bebo021999]

If the second WB is opened by File/Open or New --> that is OK for copy/paste btw them
But if the second WB is opened by starting new MS Excel window --> that may be your problem.

[jaslake]

Hi Clovis

Check how your Options are set...
2016-08-30_14-36-36.jpg

[humdingaling]

bebo was spot on in accessing what is happening
based on your description

Some colleagues prefer working with 2 workbooks side by side (one on each screen) than 2 files arranged in one workbook spread over 2 screens out.

i can tell this is exactly what is happening

your colleagues are opening seperate instances of excel to have dual screen excel
this was one of the very annoying "upgrades" i found with excel 2010

[humdingaling]

unfortunately there is no work around that i know of

https://ion.icaew.com/itcounts/b/web...ultiplescreens

Working with two screens

Generally, it’s easiest to work with two screens if you have a defined purpose for each: e.g. emails and administrative work on one screen; principle work on the other. Or you could have an original document shown on one and a marked up version on the other.

Excel itself won’t let you split it across two screens as a default – it will always display itself all on one screen or the other. However, you can run two instances of Excel to get this to work. To do this, open Excel and open your first file as normal. Then, instead of opening the second file from within Excel, go to the Start menu and open Excel from there again. This will create a second copy of Excel which you can display on the secondary screen. You can open the second document from there.

Be aware that running two instances of Excel does have some drawbacks. Firstly, you can’t write formulas that link between the two instances. Secondly, if you copy formulas from one instance of Excel, you can only paste their values into the other instance, and not the formulas themselves.

if there is any comfort that fixed it in excel 2013 apparently

[xlnitwit]

Hi,

It is also possible to stretch the Excel application window across two screens as long as it is not maximised. You may then have one workbook open on each screen whilst maintaining all the usual copy and paste functionality.

[clovis]

Hello,
@xlnitwit: as I wrote "...than 2 files arranged in one workbook spread over 2 screens out. " that's exactly what I told to my colleagues to do.
@humdingaling : thanks for the info.

This problem is a reason for some to keep using Quatro-pro.
I guess they'll have to wait for an upgrade to 2013.
Thanks you guys

[hvietkhoi]

...not sure if feasible, but a thought that might be worth trying out.

As you are probably aware, you can open up two Excel instances and have them on full screen on each monitor side by side.
What if the Excel file is a shared file, which allows two users to make changes simultaneously?
They can open the file in both instances (with drag and drop instead of double click)

Not sure, if the changes are immediately visible...?

[xlnitwit]

Hello,
@xlnitwit: as I wrote "...than 2 files arranged in one workbook spread over 2 screens out. " that's exactly what I told to my colleagues to do.

I admit I did not follow that. One workbook is one file. You may only have two files in one workbook by embedding them which did not seem a relevant option. My apologies.

[clovis]

Hello guys,
@xlnitwit : it's no problem. My wording is probably not 100% good English.
@hvietkhoi : I'm not sure if an Excel shared file can be edited at the same time by several users.
When an "MSOffice"-file is open, a lock is created to secure it from being edited by other users - I thought.
Thanks to all,
We'll be moving to Office 2016 in a short time.

[mikeTRON]

xlnitwit, essentially you open ONE instance of EXCEL. Then open BOTH files in it. THEN stretch the window to cover BOTH monitors, THEN you can put one file on each monitor within the ONE instance of excel. It is obnoxious but it works. Upgrade to 2013 and it is all taken care of natively.

[xlnitwit]

@mikeTRON,

Of that, I am aware. It is in fact precisely what I suggested. What I had not understood was that that was what clovis had already suggested as a workaround to his/her users.

[mikeTRON]

Oh I misunderstood.

[xlnitwit]

You and I both, it seems!

[clovis]

moving to Office 2016.