Broken spreadsheet formulas before a milk-payment disbursement

The formula =D2J1 uses a relative reference to J1. When the row is copied down, Calc changes J1 to J2, J3 and so on. For row 2 it multiplies by the rate in J1, but for row 3 it multiplies by J2, which is not the rate - it is probably blank or another number. Sorting by Name moves rows but does not repair the broken references; formulas may be recalculated against different neighbours, so amounts change. If a moved row lands where the relative reference points to a blank or zero cell, the amount becomes zero or wrong; that explains the changed totals and 17 wrong payments. The smallest fix is to make the rate absolute: =D2$J$1. Then every row uses the same rate cell even after sorting or copying. A better design puts the rate in a named cell or a separate parameters sheet, but $J$1 is the immediate correction. The team should also check whether any amounts were pasted as values after sorting, because values will not update if the rate changes.

Work on a copy, never the live sheet. First reconcile litres: total the Litres column and compare with the collection register; a difference points to missing or duplicated rows. Second, find duplicates with =COUNTIF($A$2:$A$301,A2)>1 on Member ID, then compare each duplicate against the register and the farmer's milk slip. Keep one correct row; do not simply delete the second without checking which bank account and litres are right. Third, trace the ₹4,500 shortfall by calculating the expected amount per member (litres × correct rate) and comparing with the sheet; group differences by route or by entry operator to find a pattern. Fourth, fix bank account numbers by formatting the column as Text before entry, re-entering leading zeros, and validating length against the passbook; do not let Calc convert them to numbers. Finally, produce a control total: sum of Amount must equal sum of (Litres × rate). Hold payment for any row that cannot be reconciled by the deadline and pay the rest. Get a second person to sign off the reconciled statement.

For tomorrow, repair the existing sheet rather than rebuild: change J1 references to $J$1, remove or correct the 9 duplicates, fix bank account formatting, and run the reconciliation. A rebuild under a one-day deadline risks new formula errors and delays disbursement. Immediately after disbursement, rebuild with a proper structure: a Member Master sheet (Member ID, name, bank account as text, route) and a Monthly Entry sheet (Member ID, litres) linked by VLOOKUP/XLOOKUP; a separate Rates sheet; data validation on Member ID (list from master) and litres (whole number within a plausible range); conditional formatting for duplicates; protected formula cells; and a daily backup with version dates. Use a unique Member ID as the key, not names. Add a reconciliation column that flags differences between calculated and register totals. This prevents recurrence because names and bank details are entered once, formulas cannot be overwritten accidentally, and duplicates are blocked at entry.