Transfer of Knowledge Underground Wiki

Spreadsheet

Replace dates with incremented year

Your spreadsheet has a column with dates, starting with say A4 with 1/2/2019 and you want to replace the dates in the this and the rest of the column with the next year, e.g., 2020.

Insert new (temporary) column to right of dates column (it's a new and blank column B)
Apply this here formula in new column B next to the first date you want to change. Say column A row 4 has 1/2/2019. Put this formula in column B row 4:
```
=DATE(year(A4)+1,month(A4),day(A4))
```
Select the cell B4 and drag down to select all rows in column B that you want to use to replace column A's dates
Control-D (pastes the formula down the column). You should see the updated dates in this new column B.
Select all of the column B cells you have changed, and copy to clipboard
Put cursor in first date you want to change, that is, in cell A4
Edit > Paste Special > Paste Special. Make sure Paste all is not checked and Formula is not checked. Paste. OK the overwrite.
You can now safely delete this column B.

Give a Cell a Name and Use It Elsewhere

It's easier to name a cell so that (a) references make sense and (b) you can move the cell but retain the reference.
for example referencing payment totals when adding/deleting rows/columns around them.

How to name a cell

Select the cell
Upper-left corner, see the letter-number (e.g., D23) of the cell. Type the name you want in this spot (e.g., CCjan)
Repeat as needed

Alternatively you can select the cell, Sheet > Named Ranges and Expressions > Define

How to use a named cell

Select the cell to reference the name
Type = and start to type the cell name (e.g, C C j a)
When the desired name is the only choice, press enter.

Note that names can be used within formulas.

Table of Contents

Spreadsheet

Replace dates with incremented year

Give a Cell a Name and Use It Elsewhere