title: MS Excel: Getting content of last cell of a column and make it update automatically
body: <p>Here's my use-case:</p>
  
  <ul>
  <li>In ws "Z" I would like a given cell to retrieve the content of the last (non-empty) cell in ws "A" for column "C".</li>
  <li>I would like also that, when I add a new row in ws "A" (column "C" for instance), the cell in ws "Z" gets automatically updated.</li>
  </ul>
  
  <p>More concretely, ws "A" contains an history table with revision numbers in column "C" and when a new entry is added, I wish the cell in ws "Z" to reflect this new value automatically.</p>
  
  <p>I'm thinking about using a formula to be written in VBA. So somewhere on SO I found something (originally a macro) that I converted into a function:</p>
  
  <pre><code>Function GetLastRow(strSheet, strColum) As String
  
  Dim MyRange As Range
  
  Set MyRange = Worksheets(strSheet).Range(strColum &amp; "1")
  
  GetLastRow = Cells(65536, MyRange.Column).End(xlUp).Value
  
  End Sub
  </code></pre>
  
  <p>Using Excel 2003, in ws "Z", I have this cell that has this formula: </p>
  
  <pre><code>=GetLastRow("A", "C")
  </code></pre>
  
  <p>But I got 2 problems with this. Although it successfully gets the content of the last cell for column "C":</p>
  
  <ul>
  <li>The formula does not get the one in ws "A", but rather the one in the current ws (i.e. "Z").</li>
  <li>Whenever I add a new entry in ws "A", the cell in ws "Z" does not get updated at all (even when forcing a recalc).</li>
  </ul>
  
  <p>The only way I could update the cell, is to type Enter on it! 8v|</p>
  
  <p>Also I tried hard-coding the worksheet name in the formula directly but it still fetches the last cell in the current ws... 8v(</p>
  
  <p>Am I doing something wrong?
  Or maybe I should proceed another way?</p>
  
  <p>I would appreciate if you could help me getting this use-case to work. Thanks.</p>