Google-sheets – Lock formulas in Google Spreadsheets

I would have a =SUM column that shows the total expense for each line. The sum is made up of all the following columns. The number of amounts on each line can be different, so that you can record tips, sales tax, or other amounts when they are applicable. Attach a note to these amounts describing what they are.

  | A           | B           | C          | D               | E               | F
1 | Date        | Description | Total      | Nominal charge  | Extra charge 1  | Extra charge 2        | ... | Extra charge X
2 | 12/01/2013  | Lunch       | =SUM(D2:2) |    $30          |  $5 (Note: Tip) |  $2 (Note: Sales tax) |
3 | 12/02/2013  | Dinner      | =SUM(D3:3) |    $50          |  %8 (Note: Tip) |                       |

So when recording a new expense, simply copy the formula from the previous row.

Note that the SUMfunction is open-ended, it will sum all numbers from and including column D. This means that all columns right of column C should be numbers - the description, date, and other information you want to record, must be put in columns left of column C.

If you should want to only record the total expense, simply write the amount in the SUM column.

If you want to automize the formula insertion (instead of dragging it down to every new row), you could use a script similar to the one listed here. Then, when you insert a new row, the formula is automatically inserted for you.

When the following script is installed, the formula from row 2 is copied and inserted when you insert a new row, adjusted for the new row number:

var FORMULA_COLUMN_IDX = 2; // The index of the formula column. 2 is column C.

function onModification(event) {
  var sheet = event.source.getActiveSheet();
  if (event.changeType == "INSERT_ROW") { // <-- This is true when a new row is inserted
    var templateFormula = sheet.getRange(2, FORMULA_COLUMN_IDX + 1).getFormulaR1C1();
    fillEmptyRows(sheet, templateFormula);
  }
}

function fillEmptyRows(sheet, templateFormula) {
    var range = sheet.getRange(1, FORMULA_COLUMN_IDX + 1, sheet.getMaxRows(), 1);
    var formulaColumnFormulas = range.getFormulasR1C1();
    var formulaColumnValues = range.getValues();
    for (var i = 2; i < formulaColumnValues.length; i++) { // Loop through all rows, starting at row 3
      if (formulaColumnFormulas[i][0] == "" && formulaColumnValues[i][0] == "") { // The formula cell is empty, so insert the formula from row 2
         formulaColumnFormulas[i][0] = templateFormula;
      }
    }
    range.setFormulasR1C1(formulaColumnFormulas);
}

To install the script, click Tools -> Script editor, and select Spreadsheet. Paste the above code in the script editor window. The script must be installed as an event handler: Click Resources -> Current project's triggers -> Add new trigger, select the onModification method and select On change from the right-most dropdown menu:

OK, this was a long explanation, but I have set up an example spreadsheet to demonstrate.

I think this is what you want. A spreadsheet the generates a column of rand() numbers. Then look up the rand() number and return another value.

In the following instruction be sure to keep the $s for absolute referencing.

1. In cell E2 enter the upper limit of the random numbers (I used 7).
2. In cells C2 to C8 enter the numbers 1 to 7 (since the upper value is 7).
3. In cells D2 to D8 enter the values you want to return. In this case I used names.
4. In cell A2 enter the formula: =int(RAND()*$E$2)+1 (where cell E2 holds the upper limit of the random numbers).
5. Copy this formula down as far as needed.
6. In cell B2 enter the formula: =vlookup(A2,$C$2:$D$8,2) (where cells C2 to D8 hold the substitution values). If the random number generated is 1 then this returns the name Abe.
7. Copy this formula down as far as needed.