75ZT Community

raistlin · Posted 14 Jul 2012, 14:48 #1

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?

Mick · Posted 14 Jul 2012, 15:50 #2

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.

Mick · Posted 14 Jul 2012, 15:57 #3

There's also a tutorial on this seeming difficult issue.

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

raistlin · Posted 14 Jul 2012, 16:18 #4

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:

Mick · Posted 14 Jul 2012, 16:23 #5

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

raistlin · Posted 14 Jul 2012, 16:37 #6

Sorry Mick I can't see which fields have the calculations in them

Mick · Posted 14 Jul 2012, 16:51 #7

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.

raistlin · Posted 14 Jul 2012, 17:18 #8

Not in this one it doesn't Mick :panic:

Bernard · Posted 14 Jul 2012, 17:25 #9

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.

Mick · Posted 14 Jul 2012, 18:04 #10

raistlin wrote:Not in this one it doesn't Mick

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

raistlin · Posted 14 Jul 2012, 18:39 #11

Hmm... downloading Open Office, at the amazingly fast rate of 6kb/sec. I hope it works better than it downloads

Mick · Posted 14 Jul 2012, 18:49 #12

If it is anywhere near it's OSX counterpart Neo Office then you may find yourself quite enamoured with it.

raistlin · Posted 14 Jul 2012, 18:58 #13

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

Mick · Posted 14 Jul 2012, 19:34 #14

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.

Trebor · Posted 15 Jul 2012, 00:20 #15

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

Dave · Posted 15 Jul 2012, 13:20 #16

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:

raistlin · Posted 15 Jul 2012, 13:29 #17

Thanks Dave

Actually, if you are that good, you might be able to work in a couple of extras that I've been contemplating

Dave · Posted 15 Jul 2012, 14:50 #18

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!

raistlin · Posted 03 Aug 2012, 16:01 #19

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

Dave · Posted 03 Aug 2012, 18:09 #20

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)

75ZT Community

75ZT Community

Excel experts please help. by raistlin