title: Write formula to Excel with Python
body: <p>I am in the process of brain storming how to best tackle the below problem. Any input is greatly appreciated.</p>
  
  <p>Sample Excel sheet columns:</p>
  
  <pre><code>Column A  |  Column B  | Column C
  Apple     |  Apple     |
  Orange    |  Orange    |
  Pear      |  Banana    |
  </code></pre>
  
  <p>I want Excel to tell me whether items in column A and B match or mismatch and display results in column C. The formula I enter in column C would be <code>=IF(A1=B1, "Match", "Mismatch")</code></p>
  
  <p>On excel, I would just drag the formula to the rest of the cells in column C to apply the formula to them and the result would be:</p>
  
  <pre><code>Column A  |  Column B  | Column C
  Apple     |  Apple     | Match
  Orange    |  Orange    | Match
  Pear      |  Banana    | Mismatch
  </code></pre>
  
  <p>To automate this using a python script, I tried:</p>
  
  <pre><code>import openpyxl
  wb = openpyxl.load_workbook('test.xlsx')
  Sheet = wb.get_sheet_by_name('Sheet1')
  for cellObj in Sheet.columns[2]:
      cellObj.value = '=IF($A$1=$B$1, "Match", "Mismatch")
  wb.save('test.xlsx')
  </code></pre>
  
  <p>This wrote the formula to all cells in column C, however the formula only referenced cell A1 and B1, so result in all cells in column C = Match.</p>
  
  <pre><code>Column A  |  Column B  | Column C
  Apple     |  Apple     | Match
  Orange    |  Orange    | Match
  Pear      |  Banana    | Match
  </code></pre>
  
  <p>How would you handle this?</p>