title: How to reference value in cell to the left within a formula
body: <p>I have 2 sheets in my spreadsheet. Sheet2 pulls information from Sheet1. In sheet2, there are 2 columns. Column A has company names. Column B has a formula which searches for the company name of that row within sheet1, and sums the values from that row in sheet1.</p>
  
  <p>I have been able to achieve this with the following formula.</p>
  
  <pre><code>=SUMPRODUCT((Sheet1!B:B=A1)*(Sheet1!F:F))
  </code></pre>
  
  <p>This works fine, however I have to manually type "A1" into the formula. For the other rows, I would have to write B1, C1, D1 etc.</p>
  
  <p>I have searched for how to reference the cell to the left, and I found this formula...</p>
  
  <pre><code>=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())), 0, -1)
  </code></pre>
  
  <p>This seems to work as a standalone formula in a cell, but I cannot figure out how to incorporate this into the <code>SUMPRODUCT</code> formula. Anything I try gives errors. I need something like this.</p>
  
  <pre><code>=SUMPRODUCT((Sheet1!B:B=(=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())), 0, -1)))*(Sheet1!F:F))
  </code></pre>