title: How can I create a formula using VBA to reference a dynamically offset range of cells?
body: <p>This is a very strangely specific need, and the last thing I need to complete my suite of new macros.</p>
  
  <p><a href="https://i.stack.imgur.com/W5s2I.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/W5s2I.jpg" alt="Example Image"></a></p>
  
  <p><em>Note: The '---' at the top of the sheet is there to represent several months of the same report going back in time</em></p>
  
  <p>As you can see in the image linked above, I have two highlighted sections. I need to make column G the sum of E and F from the <strong>previous</strong> report's numbers. Because there is a new set of data added every day, I can't reference specific cells and it must be dynamic. The larger problem here is that my number of customers will change every so often. It will <strong>only</strong> go up and it will <strong>always</strong> be in the same order; even if a lose a customer they stay on the report in the same spot.</p>
  
  <p>My only theories on how to get this done are: </p>
  
  <ol>
  <li>Find the second to last instance of customer A and define a rng based on the offset cells to the right. My problem with this is that—to my understanding—even filling that formula all the way down will just give me the one value.</li>
  <li>Adding <code>=SUM((INDIRECT(ADDRESS(ROW()-5,COLUMN()-2))):(INDIRECT(ADDRESS(ROW()-5,COLUMN()-1))))</code> to the blank cells. My problem with this is that the -5 in the offset is able to change, and even defining it by the number of blank cells will cause a mistake the first time a new customer comes on.</li>
  </ol>
  
  <p>Any insight would be very much appreciated. And please let me know if you have any clarifying questions; I'm happy to answer/edit the original post as needed.</p>