Differences

This shows you the differences between two versions of the page.

--- libreoffice_annoyances [2021.11.22 08:24] – Steve Isenberg
+++ libreoffice_annoyances [2021.11.22 08:34] – [Replace dates with incremented year] Steve Isenberg
@@ Line 1: / Line 1: @@
-=====LibreOffice Annoyances=====
+<fs xx-large>LibreOffice Annoyances</fs>
 Maybe not //annoyances// but ways of getting things done.
-====Spreadsheet====
+=====Spreadsheet=====
-===Replace dates with incremented year===
+====Replace dates with incremented year====
 Your spreadsheet has a column with dates, say B7 has 1/2/2019 and you want to replace the dates in the rest of the column with the next year, e.g., 2020.
   - Insert new column to right of dates column (new and blank column C)
-  - Apply this formula in new column C next to the first date you want to change.  Say column B row 7 has 1/2/2019.  Put this formula in column C row 7: <code>=DATE(year(B7)+1,month(B7),day)B7))</code>
+  - Apply this formula in new column C next to the first date you want to change.  Say column B row 7 has 1/2/2019.  Put this formula in column C row 7: <code>=DATE(year(B7)+1,month(B7),day(B7))</code>
   - Select the cell C7 and drag down to select all rows in column C that you want to replace column B's dates
   - Control-D (pastes the formula down the column). You should see the updated dates in column C.
@@ Line 13: / Line 14: @@
   - You can delete column C.
-===Give a Cell a Name and Use It Elsewhere===
+====Give a Cell a Name and Use It Elsewhere====
-Suppose you have a budget and on one part of the sheet you have areas for each of the 12 months where you record and total each month's credit card payments.  On another part of the sheet you have a check register; that's where you want to put the total for each month's credit card payments.  You also want to be able to move the credit card payment area around without affecting the check register.
+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.
-The solution is: give names to the 12 cells holding the month's totals and reference these in the check register.  (There may be other solutions but this is what I'm using.)
-  - Give names to the 12 monthly credit card totals
-    - Select a month's total (e.g., January)
-    - Upper left, see D23 or similar. Replace that with the name, e.g., CCjan
-    - Repeat for next month (e.g., CCfeb) etc.
-  - Go to and select a cell in the check register where you need to put a month's credit card total, e.g, January's credit card payment total
-  - Type: =CCjan and enter (you'll see options as you type the C C j)
-  - Repeat for the other months