Forum Discussion
Copying a formula to adjust the cell range automatically.
I am creating a budgeting spreadsheet. On the master list I have a formula created (*See first picture) to sum amounts in the other tabs according the category they are placed in. I want to copy this formula easily into the next month column in my master data. However as you can see in my specific data tabs the columns aren't adjacent so simply using the small square to drag and copy the formula includes the wrong cell range because it assumes the adjacent columns on the data tabs are where the data is being pulled from. Is there a more simple way to copy this formula rather than re-writing for every single cell??
3 Replies
See the attached workbook for a solution, and for more information, including hyperlinks to documentation and explanatory articles. Read the _Info worksheet, and the information below.
Using Excel's LET function in a formula allows you to do multiple calculations within one formula, in a way that is easier to understand and typically more efficient than the alternatives. Below is the formula in cell C12 of the Master worksheet. Note that I have included soft line breaks (Alt + Enter) to break it into multiple lines, making it easier to read. I used 8 "variables" to store intermediate calculations; the last line (which does not define a variable name) determines what is stored as the cell's value.=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)
Excel will modify the cell references in formulas that you copy from cell to cell, unless you use absolute references in the formula, in which both the column letter(s) and row number are preceded by a dollar sign ($), such as $AH$21. (Without any dollar signs, the reference is a relative reference.)
In your case, you do want the cell references to be modified, but only in particular ways. That involves mixed references. So when referencing the first row of the current formula (as I did when calculating the month number month_num), a dollar sign precedes the 1 only: MONTH(C$1). Regardless of where such a formula is copied, that reference will refer to the first row. Similarly, the retrieval of category name from (always) column B uses a reference of $B12.
As for the OFFSET function, it returns a rectangular range of one or more cells relative the the first argument. Review the documentation. By default, the number of rows offset is zero, so I left that parameter out. Similarly, the default number for height (in rows) and width (in columns) is 1, so I omitted the latter parameter.If you believe that you will have more than 151 transactions per month on any of the expense detail worksheets, change that number (max_rows) in the formula before you start copying it.
if you want to automate your budget,I am afraid you have to save your data in layout like below:
amount debit credit checked 100 Checking Student Loan 2023-12-24 22:19:40-65883dfc7ef15 200 Checking Home Loan 2023-12-25 07:28:35-6588bea375e68 100 a Home Loan 2023-12-28 17:15:10-658d3c9e3c7f4 then generate summary reports as your images rather than directly save all datas in report which may not be easy to handle by computer.
Your work sheet needs to be completely redesigned.
Its not using the full capabilities of Excel. [Tables, array functions etc.,]
Its quite a static design.
Without sample data the community will not be effectively help you.
