Excel 2010, Windows 7 - Can't copy formula from one particular cell in workbook A to another workbook B

Hi Experts,

I've been chasing my tail on this for days and hope someone can assist,  I've got a project I've been working on for a couple of months and have many versions of.  There is a Range of cells in the current workbook that somehow had their formulas converted to values.  When I go back to an earlier version and try to copy Cell AR8 and Paste as Formulas to the current version (these are date formulas), the Paste results in a value, and the value is not even equal to the value that was copied.  I'm attaching two versions - the older version is "EE Example - Previous.xlsm" and the newer version is "EE Example - Current.xlsm".  If I can get that working, I'll proceed to copy Cell AS8 to the Current file, too (the formulas are slightly different).  

I know it sounds simple.  The things I've tried:

- This is not the issue with the Skype Click-to_Call add-in in IE (I've never had Skype installed on this PC).
- I've tried it with all browsers closed.
- I've done an Office Repair which threw errors
- I've done an Office Uninstall which also threw errors
- I've downloaded and ran the MS Fixit for Uninstall Errors and this also threw errors
- I downloaded and ran a 3rd party Office Uninstall 'clean-up' utility and this, too, also threw errors but the Office 2010 program did finally disappear from Program & Features
- I did an Office Re-install, which also threw errors
- Thinking this was beginning to look like a virus of some kind, I ran Full scans with Avast! Anti-virus (free edition) and MalwareBytes Anti-Malaware program (Professional Edition).  Note:  Throughout this process, all relevant programs (W7, Office, Avast!, MBAM were current on Updates and patches)
- At this point, I proceeded to do a clean reinstall of Windows 7 (32 bit) and Office 2010 Pro.  Then did all of the updates (I know I should have invested the time to image my hard drive before now and after this experience I will ... just as soon as I can get past this problem and know it's not my computer)
- All of the above steps failed to correct the original Copy/Paste problem.  
- Finally, I copied the files to another computer and found that the problem repeated there, too (which told me the problem was in the file and not the computer.
- I can't even copy the formula from the source file's Formula bar and paste them to the Current file.
- I can copy other formulas (simple ones with simple formats as opposed to the more complex ones with conditional formatting in the referenced Cells AR8:AS8) from the Previous file to the same Current file, and those work.

I have no idea what this could be and hope someone can help me figure this out.




Jeffrey SmithOwnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:

I can spot couple of changes which you did from your previous to current..However without listing down all those changes..

one of the primary reason the way its happening is that you converted cell as8 as part of table..which is you select cell as8 you will have design option their and its part of the table ..And you considering the first row as header now because of which it's happening that whatever formula you apply it gets converted back to value since it's consider as a header in shape of value rather then formula..Even if you try to delete the value you will still find Column1 as a header and you cant delete it no matter what..Now if you want to apply formula..then in design option only you will have option of convert to range..just click on that and it will convert your table to a normal range and you will be able to apply formula on the same..


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey SmithOwnerAuthor Commented:
Thanks for posting Saurabh (we meet again I'm happy to say).

So, If I convert to range and apply my formula, converting it back to a Table will overwrite the Formulas with Values because they are Table Headers, correct?  So, if I want to maintain the Tables in this file (there are about 8-9 of them), then I might want to insert a Hidden Row below Row 8 that is a Pasted Values copy of Row 8, and make that the Header so that this section of Row 8 can still be formula driven?

Saurabh Singh TeotiaCommented:
Yeah you can do that or make your table skipping row-8 without headers..depending upon what your need is or what is the outcome you are trying to achieve..

And yeah we meet again.. :-)

SD-WAN: Making It Work for You

As bandwidth requirements and Internet costs grow, businesses naturally want to manage budgets by reducing reliance on their most expensive connection types. Learn more about how to make SD-WAN work for your business in our on-demand webinar!

Jeffrey SmithOwnerAuthor Commented:
Great !  I can't believe that this was the answer (and how much time I've lost as a result of that ... oh well, at least I'll be much better prepared with an image of my drive the next time I have issues.)

Without Row 8 as a header row (or a hidden copy of Row 8 in an inserted Row 9), how might I expect a Table to behave differently than normal?

Thanks again, Saurabh - you are a life saver  !!

Saurabh Singh TeotiaCommented:
I don't think the table will behave differently without a header row..However it will get difficult for you to refer the particular column in the table as in table you refer them by their header name so you will need to find the name of that column in order to refer but apart from that i don't think it will create any difference...
Jeffrey SmithOwnerAuthor Commented:
All right !  This is the best news, Saurabh.

Thanks once again.

Saurabh Singh TeotiaCommented:
You are welcome... So i'm guessing then you are all set with this one..and i can consider this one as closed...

Always happy to help..

Jeffrey SmithOwnerAuthor Commented:
Fantastic, timely competent help!  This is why I've been an EE member for 12 years !
Jeffrey SmithOwnerAuthor Commented:
Yes, it is, Saurabh,  My weekend might actually be productive now without this mystery hanging over my head. ;-)

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.