Excel experts please help. by raistlin


User avatar
raistlin
I have an excel spreadsheet file which has calculations in certain fields (ie 'cell x' times 'cell y' equals 'cell z')

My problem is that this file is a claim form and is full after seven claims at which point I print it out and send the hard copy for payment. I should then be able to delete the data in the various cells and re-use it but when I delete the data, the calculations seem to disappear as well.

Can anybody help please?
Paul

Cogito ergo sum... maybe?

Click the image to go to Nano-Meet Website
Image

Posted 14 Jul 2012, 14:48 #1 

User avatar
Mick
(Site Admin)
Pretty much what I said earlier. ;)

Click on the cell containing the data you wish to delete. Select more than one cell by holding down the "Ctrl" key while clicking on the other cells.

Go to the "Edit" menu at the top of the page and highlight the "Clear" option.

Choose "Contents" by clicking on it to delete only the data and not the formulas from the selected cells.

Posted 14 Jul 2012, 15:50 #2 

User avatar
Mick
(Site Admin)
There's also a tutorial on this seeming difficult issue.

http://www.youtube.com/watch?v=mjHPMLy9py4

Posted 14 Jul 2012, 15:57 #3 

User avatar
raistlin
Mick wrote:Pretty much what I said earlier. ;)

Click on the cell containing the data you wish to delete. Select more than one cell by holding down the "Ctrl" key while clicking on the other cells.

Go to the "Edit" menu at the top of the page and highlight the "Clear" option.

Choose "Contents" by clicking on it to delete only the data and not the formulas from the selected cells.


Did that Mick,

It wipes out everything :(

The drop down "Clear" menu give me the options to clear:-

ALL

FORMATS

CONTENTS

COMMENTS

HYPERLINKS

Perhaps I should have said that I'm using Excel 2010... you know, the one that hides all the most useful functions :lol:
Paul

Cogito ergo sum... maybe?

Click the image to go to Nano-Meet Website
Image

Posted 14 Jul 2012, 16:18 #4 

Last edited by raistlin on 14 Jul 2012, 16:23, edited 1 time in total.

User avatar
Mick
(Site Admin)
Don't delete the field with the calculation in it. once the data used in the calc has been deleted the cell with the formula should return something like ≠value≠ or $0

Posted 14 Jul 2012, 16:23 #5 

User avatar
raistlin
Sorry Mick I can't see which fields have the calculations in them
Paul

Cogito ergo sum... maybe?

Click the image to go to Nano-Meet Website
Image

Posted 14 Jul 2012, 16:37 #6 

User avatar
Mick
(Site Admin)
Clicking on a cell will show the contents in the formula bar at the top of the page, either data or a formula. I think in Excel a double click will show same within the cell.

Posted 14 Jul 2012, 16:51 #7 

User avatar
raistlin
Not in this one it doesn't Mick :panic:
Paul

Cogito ergo sum... maybe?

Click the image to go to Nano-Meet Website
Image

Posted 14 Jul 2012, 17:18 #8 

User avatar
Bernard
I had something similar and the only way I could resolve this easily was to keep a duplicate of the empty spreadsheet and save the new, completed, s/s as a new file. I could not find a way of storing a template as such, much to my annoyance.
I don't like signatures, they take up too much screen space.

Posted 14 Jul 2012, 17:25 #9 

User avatar
Mick
(Site Admin)
raistlin wrote:Not in this one it doesn't Mick :panic:


I only have experience of Excel for Mac. However I wouldn't use it in a fit. Neo Office is far superior and absolutely free
D'load Open Office, open the document with that and get your life back. Excel although a very powerful piece of kit can be an absolute dog to work with. As are all Microsoft products. IMNSHO

Posted 14 Jul 2012, 18:04 #10 

User avatar
raistlin
Hmm... downloading Open Office, at the amazingly fast rate of 6kb/sec. I hope it works better than it downloads :)
Paul

Cogito ergo sum... maybe?

Click the image to go to Nano-Meet Website
Image

Posted 14 Jul 2012, 18:39 #11 

User avatar
Mick
(Site Admin)
If it is anywhere near it's OSX counterpart Neo Office then you may find yourself quite enamoured with it.

Posted 14 Jul 2012, 18:49 #12 

User avatar
raistlin
By the time it gets here I'll have forgotten what it's for Mick :lol:
Paul

Cogito ergo sum... maybe?

Click the image to go to Nano-Meet Website
Image

Posted 14 Jul 2012, 18:58 #13 

User avatar
Mick
(Site Admin)
One of the minor hazards with open source software, the mirrors used are generally very slow. :( It's odd though as whenever an update is available for my installation it usually comes in at around 147Kbs.

Posted 14 Jul 2012, 19:34 #14 

User avatar
Trebor
In similar vein to what Bernard says start with a spreadsheet that contains the formulae and save it as a template not an ordinary excel file by changing the default from the drop down menu when you have selected save as. It then saves as a template and each time you need a new one it it will open a new spreadsheet with the formulae saved in it
Robs Pictures at :

Robs Car Gallery

click below to access nano website
Image

Planning is an unnatural process, much better to just get on with things, that way failure comes as a complete surprise instead of being preceeded by a period of worry and doubt

Posted 15 Jul 2012, 00:20 #15 

User avatar
Dave
Sounds like when you are deleting the input data, you are also deleting the formulas that are being used for the calculations!

TBH, when the Workbook was set up, it should have been done with the individual Worksheets protected, and the cells with the formulas in should be locked to prevent deletion. Better still would be a macro to delete only what was required to start over.

I'm a self confessed Excel nerd and happily spend hours working on automated solutions to instances where manual input would be much quicker, just because I can

I'm happy to take a look at your Workbook if you want to send me a copy. If you do, save is as .xls though (2003 format) as I am steadfastly refusing to move onto 2007 or 2010, as I hate the ribbon! Anything I can do with it will be compatible with what you are using!

:mrgreen:

Posted 15 Jul 2012, 13:20 #16 

User avatar
raistlin
Thanks Dave :) Actually, if you are that good, you might be able to work in a couple of extras that I've been contemplating :)
Paul

Cogito ergo sum... maybe?

Click the image to go to Nano-Meet Website
Image

Posted 15 Jul 2012, 13:29 #17 

User avatar
Dave
raistlin wrote:Thanks Dave :) Actually, if you are that good, you might be able to work in a couple of extras that I've been contemplating :)


Put my reputation on the line now haven't I?! ;)

Got the Workbook ta, and will try to do what you want with it!

Posted 15 Jul 2012, 14:50 #18 

User avatar
raistlin
I'd like to thank Dave for the work he has put into this project.

After 37 beta versions I've now got a macro enabled workbook which seems, to all intents and purposes, foolproof.

Cheers Dave, you're a star :D
Paul

Cogito ergo sum... maybe?

Click the image to go to Nano-Meet Website
Image

Posted 03 Aug 2012, 16:01 #19 

User avatar
Dave
:em:

Stop it, you're embarrassing me! ;)

TBH, as said in the PM I just sent, it's no trouble at all - I actually enjoy the challenge!

(and also as said, I have some more improvements in mind which I will work in over time)

Posted 03 Aug 2012, 18:09 #20 


Top