This is probably an embarrassingly silly question for someone who has been using Excel for decades, but I've always had confusion around this.
I have a workbook that has multiple sheets (one per bank account) that contain larger versions of what I've pasted below. The data consists of rows that contain the names of my SAVINGS goals, and columns that contain the dates when I've made contributions or withdrawals to those goals. Right after the name of the goal, there's a column that contains the current value of the savings goal, and the STARTING value (hard-coded). The current value is the sum of the starting value and all of the contributions/withdrawals to the right.
I think my problem might be solved by making the entirety of the data into a table - but, for some reason, when I highlight it and select FORMAT AS TABLE, only columns C and D (which are a named range that I use in a formula in another sheet) get formatted as a table.
Columns A and B, by the way, contain totals and a place where I can confirm that my account balance equals the sum of all my savings goal balances, for error-checking and to make the appropriate monthly additions or withdrawals from the account.
Anyway, if I try to delete a row from this (remove a savings goal), I can't just highlight the entire row and delete (because of how I'm using columns A and B). If I highlight the data from column C and over to the right as far as my dates extend, and Delete > Shift Cells Up, I get "This won't work because it would move cells in a table on your worksheet." That confuses me, because I'm pretty sure that I have named ranges but no actual tables.
If I alternately take the data from the rows below the goal that I want to delete (Bike Ins) and cut/paste up into its row (row 4), the formula in D4 changes incorrectly. Before deletion, D4 contains =E4+SUM(F4:ZZ4), correctly adding the start value with all of the withdrawals/contributions for that row. After this cut/paste operation, D4 contains =E4+SUM(F5:ZZ5), so it's referencing the correct start value but the wrong row (5) for withdrawals/contributions.
WHY? I don't have absolutes in my formula, so why doesn't the entire formula adjust up one row?
Sorry to be so long-winded. In short, why does cut/paste not correctly adjust all of the cell references in my formula that's being moved?
(PLEASE NOTE that my pasted sheet clip has hidden columns, so you see relevant data and not empty columns.)
I have a workbook that has multiple sheets (one per bank account) that contain larger versions of what I've pasted below. The data consists of rows that contain the names of my SAVINGS goals, and columns that contain the dates when I've made contributions or withdrawals to those goals. Right after the name of the goal, there's a column that contains the current value of the savings goal, and the STARTING value (hard-coded). The current value is the sum of the starting value and all of the contributions/withdrawals to the right.
I think my problem might be solved by making the entirety of the data into a table - but, for some reason, when I highlight it and select FORMAT AS TABLE, only columns C and D (which are a named range that I use in a formula in another sheet) get formatted as a table.
Columns A and B, by the way, contain totals and a place where I can confirm that my account balance equals the sum of all my savings goal balances, for error-checking and to make the appropriate monthly additions or withdrawals from the account.
Anyway, if I try to delete a row from this (remove a savings goal), I can't just highlight the entire row and delete (because of how I'm using columns A and B). If I highlight the data from column C and over to the right as far as my dates extend, and Delete > Shift Cells Up, I get "This won't work because it would move cells in a table on your worksheet." That confuses me, because I'm pretty sure that I have named ranges but no actual tables.
If I alternately take the data from the rows below the goal that I want to delete (Bike Ins) and cut/paste up into its row (row 4), the formula in D4 changes incorrectly. Before deletion, D4 contains =E4+SUM(F4:ZZ4), correctly adding the start value with all of the withdrawals/contributions for that row. After this cut/paste operation, D4 contains =E4+SUM(F5:ZZ5), so it's referencing the correct start value but the wrong row (5) for withdrawals/contributions.
WHY? I don't have absolutes in my formula, so why doesn't the entire formula adjust up one row?
Sorry to be so long-winded. In short, why does cut/paste not correctly adjust all of the cell references in my formula that's being moved?
(PLEASE NOTE that my pasted sheet clip has hidden columns, so you see relevant data and not empty columns.)
| SvGoals.xlsx | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| C | D | E | BP | BQ | BR | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 2 | Fund | Current | Start | 7/12/2022 | 8/8/2022 | 8/9/2022 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 3 | Auto repair | 1,528.10 | 1500 | -412.82 | 30 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 4 | Bike Ins | 0.00 | 491 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 5 | Camper | 0.00 | 0 | -5043.66 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
DiscSv | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D3:D5 | D3 | =E3+SUM(F3:ZZ3) |
