title: Dynamic array formula that allows for target table expansion
body: <p>I have a worksheet where I copy data from Table1 to Table2.</p>
  
  <p>Once I have copy-pasted the data to Table2, which sits below Table1, I select the first cell in the first column of Table2 and press <kbd>Ctrl</kbd>+<kbd>Shift</kbd>+<kbd>Down</kbd> to select down to the last used cell. Finally, I apply a concatenation array formula that adds a suffix to the values of the related cells in Table1. I have recorded these Table2 steps as a macro.</p>
  
  <p>To demonstrate, Table1 looks like this:</p>
  
  <pre><code>Table1
  
     |  A      B   C   D   E
  ---+-----------------------
   1 | Name    V1  V3  V3  V4
  ---+-----------------------
   2 | Wood    10  10  10  10
   3 | wood    28  28  28  28
   4 | tree    30  45  60  68
   5 | plastic 50  50  50  50
   6 | tree    50  50  50  50
   7 | iron    64  75  75  80
  </code></pre>
  
  <p>This is the formula I use in column A of Table2:</p>
  
  <pre><code>{=concatenate(A2:A7," - A")}
  </code></pre>
  
  <p>This is the result after applying it to Table2: </p>
  
  <pre><code>Table2
  
     |  A               B    C    D    E
  ---+-----------------------------------
  25 | Wood - A        25   25   25   25
  26 | wood - A        50   50   50   50
  27 | tree - A        50   50   100  100
  28 | plastic - A     100  100  100  100
  29 | tree - A        100  100  100  100
  30 | iron - A        100  100  100  100
  </code></pre>
  
  <p><br>
  Now, when I add new entries to Table1, for example in cells <code>A8</code> &amp; <code>A9</code>: </p>
  
  <pre><code>Table1a
  
     |  A      B   C   D   E
  ---+-----------------------
   1 | Name    V1  V3  V3  V4
  ---+-----------------------
   2 | Wood    10  10  10  10
   3 | wood    28  28  28  28
   4 | tree    30  45  60  68
   5 | plastic 50  50  50  50
   6 | tree    50  50  50  50
   7 | iron    64  75  75  80
   8 | table   20  25  0   30
   9 | plastic 54  35  21  0 
  </code></pre>
  
  <p>after running the recorded macro, instead of using the new range <code>A2:A9</code>, it's using the previous recorded range (<code>A2:A7</code>), resulting in the <code>#N/A</code> errors as can be seen in Table2a:</p>
  
  <pre><code>Table2a
  
     |  A               B    C    D    E
  ---+-----------------------------------
  25 | Wood - A        25   25   25   25
  26 | wood - A        50   50   50   50
  27 | tree - A        50   50   100  100
  28 | plastic - A     100  100  100  100
  29 | tree - A        100  100  100  100
  30 | iron - A        100  100  100  100
  31 | #N/A            #N/A #N/A #N/A #N/A
  32 | #N/A            #N/A #N/A #N/A #N/A
  </code></pre>
  
  <p>This is because in the array formula, the range is not dynamic, but fixed.</p>
  
  <p>So, I would like a formula which auto-adjusts the range when new entries are added or deleted from Table1. Something similar to what I have in the recorded macro which selects all the cells in column A of Table2, before applying the array formula:</p>
  
  <pre><code>Application.Goto Reference:="R25C1"
  Range("A25", Range("A25").End(xlDown)).Select
  </code></pre>
  
  <p>I was thinking of something like the following, where I provide the starting cell of Table1 and the range down to the last cell with data is worked out automatically:</p>
  
  <pre><code>Selection.FormulaArray = "=concatenate(Range("A2",Range("A2").End(xldown)).Select,""- A"")"
  </code></pre>
  
  <p>I would like a formula solution, that I can apply to all the cells. I don't want to define variables, etc, to do this.</p>