Need help with Microsoft Excel

[neoglow]

Hi guys, I've created an Invoice spreadsheet in Excel for work, the spreadsheet has cells formatted for Quantities, Net Total, VAT and Grand Total. I have found if you type an amount into a cell but have to change it later because it is incorrect, it also deletes the formula in that cell, so when the new amount is entered it dosent automatically SUM to the other cells to give Total, VAT etc....

I have tried 'Protecting' the sheet but it dosent allow you to edit the sheet, only read it, you can allow users to format the cells on a protected sheet but that means the formula can still be edited/deleted.

I need to find a method of leaving the formula intact and only the cell contents can be changed, is this possible?

I'm using Microsoft Excel 2003 Professional.
Cheers, Mark.

[babs]

Highlight all the cells you want people to be able to edit, the go to Format > Cells > Protection and untick locked.

Now go to Tools > Protection > Protect Sheet and give it a password.

[babs]

I'm a little confused also though. If you type a number into a cell, it automatically overwrites any forumla in that cell. You can't have manually entered values and a formula in the same cell.

That's just how I'm reading it anyway.

[neoglow]

I'm a little confused also though. If you type a number into a cell, it automatically overwrites any forumla in that cell. You can't have manually entered values and a formula in the same cell.

That's just how I'm reading it anyway.

Exactly the problem I'm getting, surely there must be a way to prevent this, otherwise each time this happens you have to put the formula back into that cell again, pain in the bum!

[neoglow]

I think I've cracked it, you select the cells you want to protect with formulas, ie, the Totals column, format the cells and check 'Hidden' and 'Locked' under the protection tab. Then protect the sheet but check the box, 'Allow users to format cells'. This way people can edit the sheet but just not the column that you have locked/hidden. Make sure all the other cells that dont have formulas are not locked or hidden.

Well it seems to be working at the moment.

[babs]

I don't understand why you have a formula in a cell where people can input their own values.

[vertigo]

Of course you can't have a formula and a manually typed value in the same cell, coz as soon as you type in the cell you're over-writing the formula. You can only have one object in a cell at a time. You should be editing the value to which the formula in the cell refers for the formula to have the desired effect of the spreadsheet calculating the value correctly for you.