I have one “table” on a sheet. Many columns, many rows. Back when I had simple formulas in each cell, it was easy to add a new row. I had many options:
A) Selecting the entire bottom row of the table’s cells, then dragging the handle down. This can create one or many new rows at once.
B) Selecting the row below the table (Shift+space, or on the Edit menu), then fill cells down (Ctrl+D, or on the Sheet menu). This creates one new row. A proper selection can create multiple rows.
C) Copy the last row and Paste below. Again, adding one or many new rows is easy.
My primary question. As my dataset has grown, I was talked into using array functions (CSE, or Ctrl+Shift+Enter style functions). More efficient calculations and smaller file sizes I was told. The problem for me has been adding a new row. None of the above three options seem to work for array functions. The only option I know is:
- Select the entire array range (Ctrl+/).
- Grow the range by dragging the handle down.
- Manually update the formula to include the larger range.
That this is three steps isn’t too bad. But it seemingly must be done for each column individually. For 40 columns, this is unworkable.
My second, possibly related question. All three options update the references WITHIN the table correctly. References TO the table do not update as it grows. What options do I have for solving this? Can a SUM or LOOKUP function (on another sheet) have it’s range update automatically? Can the table’s named range update automatically? Can a second table based on this table grow with it automatically? All pointers in the right direction are appreciated!
Inserting new rows above the final row may be an option. Then I can cut & paste the last row up. Leaving a blank row at the bottom may work too, but causes other problems. Neither of these is an option if there is even one column contains an array function, because inserting rows becomes disabled.
My first question here. Help clarifying my post is also appreciated.