7 Replies Latest reply: Jul 28, 2014 6:03 AM by t quinn
Gary Nunn Level 1 Level 1 (35 points)

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)
  • t quinn Level 4 Level 4 (1,965 points)

    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.

    Screen Shot 2014-07-27 at 9.26.44 AM.png

    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

  • rbourke Level 1 Level 1 (0 points)

    I have a similar, but more fundamental issue. I want to sort my whole table on one of the columns. The instructions start with "click on the table". This simple instruction should be obvious. Anywhere I click I select only a cell, not the whole table. Any suggestions?

  • t quinn Level 4 Level 4 (1,965 points)

    Hi rbourke,

     

    While I don't understand the similarity, here is what you want to know.

    With a cell selected you will be able to select the column you want to sort. Click on the little triangle that appears on the left side of the column header. Choose "Sort ascending" if that is what you want.

     

    quinn

  • Gary Nunn Level 1 Level 1 (35 points)

    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

  • t quinn Level 4 Level 4 (1,965 points)

    Hi Gary,

     

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

    Screen Shot 2014-07-28 at 12.59.09 AM.png

    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

  • Gary Nunn Level 1 Level 1 (35 points)

    ok.. thanks again for all your help...

  • t quinn Level 4 Level 4 (1,965 points)

    you bet.

    q