How to recalculate all formulas when a single cell value changes

[hrdpgajjar]

Hi there,
I've a fittings material sheet. I have locked the maximum items additions on the base of an area field. I have set data validation rules as under,


for example :

if area is 1.0 then total allowed fitting material cost is 2000 Rs. whenever user tries to enter more items or the total cost goes beyond 2000 the data validation will not able to enter more amount or items.

All this calculations are based on entering area only.

Now my problem is, other employees do the trick here. they first enter larger area, say for 10.00 and enter all the material accordingly and then save back the area to 1.00.

My question is, how can i stop users from doing this.

i.e. all calculation must be done again when user finally saves the fitting sheet.


help appreciated


Thanks

 

[hrdpgajjar]

images attached herewith

 

[etaf]

are they manually overwriting the formulas ?
you could lock the spreadsheet so the formula cells can. not be entered without a password

Otherwise , they could change the spreadsheet to manual calculation - so it does not calculate anymore on a cell change
but as soon as thats change or F9 I think pressed it will recalculate

 

[hrdpgajjar]

are they manually overwriting the formulas ?
you could lock the spreadsheet so the formula cells can. not be entered without a password

Otherwise , they could change the spreadsheet to manual calculation - so it does not calculate anymore on a cell change
but as soon as thats change or F9 I think pressed it will recalculate

no the sheet is already locked. but area field and quantity field is unlocked as user must have to enter material quantity.

 

[etaf]

So you data validation stops material entry if set at 1.00

whenever user tries to enter more items or the total cost goes beyond 2000 the data validation will not able to enter more amount or items.

To get over this - they change to say 10

they first enter larger area, say for 10.00 and enter all the material accordingly

so now they can enter whatever they like

can you put a data validation on the Area, so that it checks the material size entered -
and then wont allow the 10 to be dropped back to 1