Let's break down why your current code isn't working first: you included a format placeholder but didn't actually use it to update the cell references in the formula. Right now, no matter which row you're writing to, the formula stays fixed with D2, D3, etc.—it never adjusts to the current row number.

Here's the corrected code that will make the cell references update automatically as you fill down the column:

for row_num in range(3, max_row + 1):
    # Use row_num to reference the previous row (row_num-1) and current row dynamically
    sheet[f'G{row_num}'] = f'=ACOS(COS(RADIANS(90-D{row_num-1})) *COS(RADIANS(90-D{row_num})) +SIN(RADIANS(90-D{row_num-1})) *SIN(RADIANS(90-D{row_num})) *COS(RADIANS(E{row_num-1}-E{row_num}))) *6371'

What changed? #

• We swapped to Python f-strings (available in Python 3.6+) to directly inject the dynamic row numbers into the formula. This means:
  • When row_num is 3, the formula uses D2, D3, E2, E3 (matching your original example)
  • When row_num increments to 4, it shifts to D3, D4, E3, E4, and so on for every subsequent row
• Every cell reference now updates in lockstep with the row we're writing to, exactly what you need.

Quick Note #

Just double-check that your max_row variable correctly captures the last row with data in columns D and E. If you haven't calculated it yet, you can loop through columns D/E to find the first empty cell, or use your library's built-in method (like openpyxl's worksheet.max_row if that aligns with your data range).

内容的提问来源于stack exchange，提问作者LZed