Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

2 NUMBERS issues i need help with

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

iMac, OS X Mavericks (10.9.4)

Posted on Jul 27, 2014 7:39 AM

Reply
Question marked as Best reply

Posted on Jul 27, 2014 8:31 AM

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.

User uploaded file

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

7 replies
Question marked as Best reply

Jul 27, 2014 8:31 AM in response to Gary Nunn

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.

User uploaded file

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

Jul 27, 2014 11:40 PM in response to t quinn

thanks t quinn for your help.. i was able to get the percentage thing fixed...


i do have two more questions if you don't mind....


1.when i am typing in the formula, why does the formula take on the column name and product name (row) fields instead of just keeping the column letter and row number i typed in.. for example, i typed in B3 x $B$1 + B3 and when i hit the check mark to save it, it changes the formula to

wholesalecostblueshoes x $B$1 + wholesalecostblueshoes (where wholesale cost is the Column and blue shoes is the product name in that row.. i mean the formula still works, but it's kind of an annoying housekeeping issue... not all the boxes with the formula in them change to this, some retain the Column letter and row number.. but some switch to what the name of the column and row are...


2. i added a check mark column so that when i am working on updating the prices, i can click the check box to remember that that row has been updated... now, the next time i need to update the prices, all the checks are checked.. what i want to know is, is there a way to have a separate checkbox like up by the MARKUP PERCENT box that i can click to UNCHECK all the boxes... you know so i don't have to manually click on every checkbox to uncheck them... kind of like a "check all" "uncheck all" kind of thing...


thanks,

gary

Jul 28, 2014 12:13 AM in response to Gary Nunn

Hi Gary,


1) you want to go to preferences>Cell References and uncheck "Use header names as labels."

User uploaded file

2) I assume you are updating your wholesale prices. I don't think there is anyway to change a checkbox with a formula. If it were me I would let all the check boxes stay checked until I updated prices and then uncheck them as I did the update.

If it is important to you to have them unchecked I would post another question asking for an applescript or automator service to do that. SG is a frequent poster and has posted many scripts for all kinds of situations. I am sure he could create one for this.


quinn

2 NUMBERS issues i need help with

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.