Copy a WPS Spreadsheet Budget Formula Across Months With Dynamic Column Offsets
Question details
Copy a monthly budget formula across columns while retrieving category totals from source worksheets whose monthly data blocks are separated by a fixed number of columns.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A master budget sheet needs to total the same category for each month from multiple account worksheets, such as RBC Visa, RBC MC, CIBC Visa, and Debit.
- Observed behavior
- The formula must use the month in the column header to move each source range by the correct monthly column offset while preserving the header row and category column references.
Make sure each source worksheet uses the same monthly block width and that the month headers in the master sheet are valid dates. Confirm that the category labels in column B match the category text used on every source sheet.
Use LET, OFFSET, and SUMIF to calculate each monthly category total
Use this method when each account worksheet stores month data in repeating blocks of four columns and you want one formula that can be copied across the budget grid.
The formula gets the month number from the current column header, converts it to a four-column offset, and uses OFFSET to select the matching category and amount ranges on each account sheet. LET names the intermediate values so the formula is easier to review and maintain.
On the master budget sheet, select the first total cell, such as C12, and enter: =LET(month_num,MONTH(C$1),category,$B12,col_offset,(month_num-1)*4,max_rows,151,RBCV,SUMIF(OFFSET('RBC Visa'!$C$4,,col_offset,max_rows),category,OFFSET('RBC Visa'!$B$4,,col_offset,max_rows)),RBCM,SUMIF(OFFSET('RBC MC'!$C$4,,col_offset,max_rows),category,OFFSET('RBC MC'!$B$4,,col_offset,max_rows)),CIBC,SUMIF(OFFSET('CIBC Visa'!$C$4,,col_offset,max_rows),category,OFFSET('CIBC Visa'!$B$4,,col_offset,max_rows)),debit,SUMIF(OFFSET(Debit!$C$4,,col_offset,max_rows),category,OFFSET(Debit!$B$4,,col_offset,max_rows)),RBCV+RBCM+CIBC+debit)
Keep C$1 as a mixed reference so copying right changes the month column but continues to read row 1. Keep $B12 as a mixed reference so copying down changes the category row but continues to read column B.
Select the formula cell, then drag the fill handle across the monthly columns and down the category rows. WPS Spreadsheet updates the relative parts of the references automatically.
Change max_rows, currently set to 151, to a larger number if any source worksheet can contain more than 151 transactions in a monthly block.
Consolidate transactions into one table for easier budget reporting
Use this approach when you can redesign the workbook and want to avoid maintaining separate month blocks and multiple worksheet references.
Build Copyable Monthly Budget Formulas in WPS Spreadsheet
WPS Spreadsheet lets you create a reusable monthly budget formula with LET, SUMIF, and OFFSET, then fill it across month columns and down category rows. Mixed references keep your headers and category labels aligned as the formula moves.
- 1. Open the master budget workbook: Launch WPS Office and open the budget workbook in WPS Spreadsheet.
- 2. Select the first summary cell: Click the first month-and-category result cell, such as C12, where the budget total should appear.
- 3. Enter the LET budget formula: Type or paste the LET formula using MONTH(C$1), $B12, and the required source worksheet names, then press Enter.
- 4. Fill the formula through the report: Drag the cell fill handle across month columns and down the category list to calculate the remaining totals.
Frequently Asked Questions
Why does the formula use MONTH(C$1) instead of a fixed month number?
MONTH(C$1) reads the date in the current month header. When you copy the formula to the right, C$1 changes to the next header cell while row 1 remains fixed, allowing the offset to change automatically.
What does (month_num-1)*4 do in the budget formula?
It converts the month number into a column shift for four-column monthly blocks. January returns 0, February returns 4, March returns 8, and each OFFSET range moves that number of columns from its starting cell.
How do I change the formula if each month uses five columns?
Replace (month_num-1)*4 with (month_num-1)*5. Also verify that each OFFSET starting cell points to the first category or amount column of the January block.
Why are C$1 and $B12 written with dollar signs?
They are mixed references. C$1 locks the header row while allowing the month column to change. $B12 locks the category column while allowing the category row to change when the formula is filled down.
What should I do if a source sheet has more than 151 transactions?
Increase the max_rows value in the LET formula, such as changing max_rows,151 to max_rows,500. The category and sum ranges created by OFFSET must be large enough to include all transactions.
