How do I copy formula across columns and rows, adjusting cell references and sheet names
I can't figure out how to copy a formula using a mixture of absolute and relative references and sheet name references.
I have a sheet that's a summary of the other sheets in the book, each component sheet representing a month of the year. I need to populate the summary sheet by copying cells with references to the other sheets. The formula columns need to reflect the associated columns from a fixed row on each sheet, while the formula row needs to reflect the sheet selection. The sheet names are based on the month name.
The workbook has one sheet for each month of the year, Jan, Feb, etc. Each of those sheets is identical, and I am pulling values from the month summary row (row 4) of each month's sheet. The summary row contains month totals for different accounting categories in consecutive columns starting in column E.
Each row of the summary sheet contains the summary row from the associated month sheet.
In other words, I have this: cell E5 is =IF(Jan!E4>0,Jan!E4," ")and I want the cell below it (E6) to be =IF(Feb!E4>0,Feb!E4," ").
Then cell 'F5' (to the right of E5) would be =IF(Jan!F4>0,Jan!F4," ").
I'm attaching screenshots of the summary page and one of the monthly sheets.
3 Answers
Rey Juna's answer explains how to do this with a lookup table. Here is an alternate approach to derive the reference addresses without a lookup table. The general structure of the formula is similar. Much of it is dictated by translating your formula locations to cell references (note that the location translations in my formulas may now be a little different from Rey's because the description in the question changed a little).
- You want formula row 5 to refer to January (month 1), so we need to subtract 4 from the formula row.
- You want to pull data column-for-column, starting in column E, but always from row 4 of the target sheet.
The description in the question changed, so I'll use a slightly different method with INDIRECT. INDIRECT has a feature that allows referencing cells with the so-called R1C1 format, which is handy for this kind of requirement. You can specify row and column numbers easily, and do relative addressing.
INDIRECT has an optional last parameter used for indicating the style of cell references. If it is FALSE or 0, it indicates R1C1 style addressing. Within the INDIRECT string, R4C[0] refers to row 4 and the same column as the formula (zero offset).
Otherwise, the main difference here is how to derive the sheet names via a formula instead of a lookup.
The key to that is this formula:
TEXT((ROW()-4)*28,"mmm")The row minus 4 was explained above, translating formula location to month number. We need to turn the month number into a date that falls within that month (which can be in any year, we just need a day of the year). Multiplying by 28 does that. All months but February during a non-leap year have more than 28 days, but that's good enough to guarantee that the resulting day number is in the right month.
(Note that this trick works for translating 1-12 to Jan-Dec, but it would need to be tweaked if your starting month is something other than January or you have multiple consecutive years; you can't just adjust the row offset. See the addendum below.)
The TEXT function formats the result as the three letter month abbreviation.
Putting that together, the actual formula is:
=IF(INDIRECT(TEXT((ROW()-4)*28,"mmm")&"!R4C[0]",0)>0,INDIRECT(TEXT((ROW()-4)*28,"mmm")&"!R4C[0]",0),"")You can copy and paste this formula into cell E5, then just copy and paste or drag to fill-in your matrix. You shouldn't need to adjust the formula unless the worksheet layout changes.
Addendum: If your months run something other than Jan-Dec (like fiscal years), and/or you have multiple consecutive years, here's another trick to use the conversion shown above of month number to month abbreviation.
- Make the first row adjustment to translate row number to starting month number. Say your first formula row is 5 and your starting month is October, so you would use ROW()+5.
- Wrap that with the MOD function to leave the remainder after dividing by 12 (December will come out zero, but that still works):
MOD(ROW()+5,12). The result is that every row points to the right month number. - Use that in the TEXT function:
TEXT(MOD(ROW()+5,12)*28,"mmm").
This approach requires a list of abbreviated month names, which I put in cells A1 through A12 of a sheet that I named ListMo, like this:
| | A |
|---:|:---:|
| 1 | Jan |
| 2 | Feb |
| 3 | Mar |
| 4 | Apr |
| 5 | May |
| 6 | Jun |
| 7 | Jul |
| 8 | Aug |
| 9 | Sep |
| 10 | Oct |
| 11 | Nov |
| 12 | Dec |Here's the formula that would go into your cell E7:
=IF(INDIRECT(INDEX(ListMo!$A$1:$A$12,ROW()-6,) & "!E" & COLUMN()-1)>0,INDIRECT(INDEX(ListMo!$A$1:$A$12,ROW()-6,) & "!E" & COLUMN()-1)," ")
Explanation
INDEX(ListMo!$A$1:$A$12,ROW()-6,)
Index allows you to select one element from an array/list. Row() just returns the number of the row your formula is in and the -6 is the offset needed to get row 7 to equal the first element in the list, "Jan".
COLUMN()-1
Just like Row(), Column() will return the number of the column that your formula is in and the -1 is the offset needed to get column E, or 5, to equal the 4 inE4.
Indirect allows you to put all this text together with & and then read that as a cell reference.
In the case of cell E7 it would resolve like this:
INDIRECT(INDEX(ListMo!$A$1:$A$12,1) & "!E" & 4) which then would equal Jan!E4.
The Indirect formula has to be done twice because the that cell reference is used twice in your formula.
What all of this allows you to do is to drag the formula right and yet get the cell reference to move down which is not the default behavior when dragging formulas in Excel. And dragging the formula down now selects the next sheet name and sheet names can't be incremented by default dragging.
5The $ sign in the cell address fixes the cell or row reference. If you mean for example, to refer to a cell in row 4, and copy that formula, then you might refer to the cell E$4. When this is copied down the column, it would give always E$4. Copying it across a row gives E$4, F$4, G$4.
If you were constructing a thing with reference to a column of months, (off the chart), you might refer to something like (IF $A1=E$4 ...). This when copied downwards would change the 1 so to point to A2, A3, ... which would contain the months. E4 would continue to act as the test variable, but in the next column, (when the column is copied), it would be F$4, pointing to a new test.
