How include FORMULAS (Excel) worksheet files backups, etc.

Just made horrendous discovery regarding my (Excel) worksheet files:

Saving/Backups/Copying/Transfers of all kinds do NOT include FORMULAS!

Contents impecable, but no formulas when update/change cell contents.

What have I missed, or what doing wrong?

Office 2000 for Dummies, page 246, much info re copying FORMATS, but that's not the problem.

Will appreciate help. Sincerely, RO

 

Check out www.vgupta.com

 

Fair dinkum, Aussie!

Thanks for your info although it's far beyond my experience level.

All I want to know is just how, when I move an Excel worksheet file, it will include FORMULAS.

Whether Save As to Thumb drive/Backup to disk/transfer desktop to laptop, or even sending as Attachment to myself, no formulas appear. File(s) appear beautifully, but without a single formula.

Latest wrinkle is message box saying "Cannot copy because Read Only..."
NOT SO. Checked at Properties and that's NOT checked, nor are either of the other two boxes.

What disturbs me the more is the lack of reference to this particular subject wherever I search-- booklets/FAQs/Forums/books such as Office For Dummies (which on page 246 has cleasr directions on Copying FORMATS. That's NOT the problem; format is very different from FORMULA.

Cordially, RO

 

That site is way beyond my capabilities, in Excel, as well.
Although I consider myself pretty expert in Excel.
Guess I can handle the basics very well. Bazza

 

Not sure I understand the question I've never had any trouble wither saving excel calculations or transferring them to another math program like mathcad.

What you have to realise is that spreadsheets don't have variables per se,
they have cell contents. So an excel 'formula' sin(q3) refers to take the sin of the contents of cell q3 or some such. Of course you have to have a home for this value i.e. another cell. This is like asignments statements in programming which are not true formule in the mathematical sense either.

Thus the statement r15= sin(q3) assigns the value of the sin of whatever in in cell q3 to the cell r15

Hope this helps if not post again explaining what you are trying to do.


Studio T

 

excel will drop formulas created in other spreadsheet programs if it can not convert the formula and your result will be just the value.

Formulas with macros will also give you problems when transferring or copying from one file to another as any links to other spreadsheet data sources for using the update function

As well as formulas in spreadsheets sent via email and or accessed from a web site.

 

Well you guys, thanks for your messages. Apparently I can't expect a promotion from E-2!

Bigbazza, I don't take seriously your comment about poor Excel knowledge.

Studio T, your mathematics are impressive, but way beyond me. However, as to your question of what I'm trying to do permit a restatement:

Saving/Backups/Copying/Transfers of all kinds do NOT include FORMULAS!
Contents impecable, but no formulas when update/change cell contentsl

Jewlzs, I grasp your meaning, but not the conclusion . Windows differentiates ABSOLUTE from RELATIVE references, but only applies it to Formats. Why not to Formulas, AS DOES APPLE (or did on my Apple IIc)?

Found and use partial solution, a work-around, whereby I send myself an email with Attachment -- for each of the spreadsheets. Occasional Invalid address or (alleged) Security Restrictions (!) interpose, but it's preferable to rentering literally over a hundred formulas per spreadsheet.

Cordially,

 

Attached is a very simple excell which take the number in cell a1, squares it and multiplies the result by 15, placing that result in cell b2. I have had no trouble saving it.

Try it

Then post a simplespread to demonstrate your difficulty.

The 'function' in b2 must only refer to cells that already are part of the worksheet, i.e.can refer back but not forwards.

Studio T

 

sorry i can't seem to upload excell files so I ahve renamed it book2.txt.

save it and rename it back to book2.xls

Studio T

 

Stick with studiot and Jewlzs, their expertise is well ahead of mine. Bazza

===

 

I'm a little lost as to what you mean here - so far as I know, Excel does use absolute values in formulas but I don't see that absolute versus relative has any relevance to formats. Is there something I don't know about Excel? confused

(Edit: Let's rephrase that last question... I'm sure there's LOTS about Excel that I don't know but is there something I don't know in regards to this?? )

 

Of course Excel uses both types of referencing - It would be pretty useless as a spreadsheet if it didn't.

I chose absolute referencing in my simple example to keep it simple.

The difference is also simple, when you copy the contents of a cell and paste it somewhere else.
With absolute referencing the copy in the new cell refers to the same cells sources as those in the original.
With relative referencing Excel automatically adjusts the references to their new home.

Thus copy A2 to B2

in cell A2 =2*$A$1 copies =2*$a$1 absolute referencing

in cell A2 =2*A1 copies as =2*B1

Excel also offers various different paste options to modify this simple concept.

Studio T

 

Thanks again, Studio T, but what I see is my failure to describe the problem.
It's much simpler than you make it appear, and I'll try to do better here.

There is no difficulty of any kind with any of a multitude of formulas on my spreadsheet of five pages in one case, three in another. All do just what they are supposed to, and I've been doing these spreadsheets month;ly for more than a few years.

When each file is Saved As (or Backuped or Copied or Transferred) all is normal and routine. There each file appears exactly as it should. Only recently did I wakeup to find one big surprise!
At any of the cells containing a formula there was/is an amount -- simply that; no formula showing its derivation.
I realize this discovery only came because I had no previous need to change a figure on one of the copies.

Cordially, RO

 

Hi Rodell, Hi Chookers,

The best way to get a handle on things is often to try a simple example.

My last post was about Chookers comment and my attempt to straighten referencing out in as simple a way as I could.

Try the examples if you don't understand what I have written, it will take less than 2 minutes, start to finish and seeing it in action is worth a thousand words.

With regard to Rodells original query did you try the simple example I set up?

You will always get a number in shown in cell b2 of the sheet, but if you alter the number in a1 Excel recalculates the number in b2. The 'formula' is still there. In fact it appears in the data bar at the top of the sheet, when you select b2.

Again try it and prove it to yourself. then post a simple example showing your problem.


Studio T

 

Okay, well looks like I wasn't very clear either.

Firstly, I meant absolute references, not values and I also made it sound like Excel only uses absolute values, which obviously is rubbish! Sorry about that, Chief!

Secondly, I'm asking what, if anything, references have to do with formats. If I understand correctly, rodell, you're saying that there is some connection between absolute references and formats that doesn't exist between relative references and formats? But then you say that it doesn't apply that to formulas, at which point I'm completely lost. Which I would have to say I am anyway!

Anyway, some other questions:

Do any of these spreadsheets depend on others for their data?

When you say you can't see the formulas, are you looking only in the cell or also in the formula bar along the top of the A,B,C column headings?

There must be an answer to what's going on.

 

An absolute reference means the formula in b1 is =a1*15 when copied and pasted any where on the current worksheet or within the current workbook equals just that a1*15.

A relative reference means the formula in b1 =a1*15 when dragged and filled across the row or down the column the formula b1 cell reference changes relative to the column and row number it fills example: drag and fill down b2 = a2*15, b3 = a3*15 etc relative.

If you simply highlight the worksheet and copy paste to a new workbook and/or worksheet you will only get the visual value regardless of what paste options you select.

Your only option to copy both the formulas and your values is to save as the entire workbook/worksheet.

 

Yes
Yes
No
No

I have never had any trouble copy and pasting the formula contained in a cell either to another location in the same spreadsheet or to another spreadsheet.
The resultant paste recalculates the value according to its new location and displays that value.

If you can't achieve this you are doing something wrong. Post so I can try to explain further. You can copy and paste a block or even a more complicated selection between worksheets BUT the appropriate cells have to be available in the second worksheet. It is easy to run into a situation where you screw yourself up with conflicting references if you try to boilerplate a spreadsheet like it was a wordprocessor.

I have clearly been able to save my simple spreadsheet, including the simple formula, in order to post it on MG.
If you look carefully the example contains one absolute reference (preceeded by the $) and one relative reference (without the $). Thus I wrote $a1 in the formula full absolute would be $a$1 and full relative would be a1.



Studio T