**Gary Nunn**Jul 27, 2014 7:39 AM

1. is there a way to "lock" certain colums in a spreadsheet? for example, i have a spreadsheet where column A is the product name, columns B thru M are wholesale prices that have to be input and Columns N thru Y the numbers are calculated and populated into the cells, but if i accidentally happen to click on a cell in that area, it can pop up the formula and then if you accidentally click on another cell, it adds that cell name to the formula.. or if you click it you can "pick up" the cell and move it to another location.. just wondering if there is a way to lock that section where the formula can't be changed or cells accidentally moved...

2. on this same spreadsheet... i'm wondering if there is a way to set like a "master" formula or formulas... and have the formula in the cells be kind of like clones of the master formula... i have the formula set to add a percent to the wholesale price, to get the retail price, but if want to change that percentage, right now i would have to go into every cell for every product and every size and change the percentage... and there are 12 price variations for each product.. times over 3,500 products.. you see where i'm going here.. it would be almost impossible to change all those percentages in each cell every time i needed to change the markup percentage... surely there is a way to make this happen, i just haven't been able to figure it out.. i'm thinking i may need to have a few master formulas for different brands or maybe i could just separate each brand of product into their own sheet and just have 1 master formula per sheet..

hopefully that all makes sense..

thanks for any help you provide..

gary

Hi Gary,

You can lock a whole table but they are easy to unlock. Having one table for data entry and another to generate a report is a way around this. If you end up doing data entry in iOS you will find that a data entry form will not let you edit formulas so you are good there.

As far as modifying your formulas on the fly to adjust for a change in markup percentage, that is an easy one. It will require you to change your formulas once and to add a cell for your markup.

notice the reverence to $B$1. This is an absolute reference that will always refer to this cell even as you drag the formula down to fill.

hope this helps,

quinn