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 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:
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:
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: