Incrementally increase column letters for SUM formula

Question

I'm trying to create a KPI sheet where one of the cells need to sum cells in another sheet, depending on which month it is, to create a YTD-value. So for each month this needs to sum another column to the right.

Right now I use a SUM(Sheet2!BE4:BI4) formula which I have to manually drag 1 column to the right each month, so next month it will be SUM(Sheet2!BE4:BJ4)

I've done a cell which retrieves which cell it should use with the following formula which in this case returns the text BI: =CONCATENATE(CHAR(66);CHAR(68+MID(TEXT($D$18;"DD/MM/YYYY");5;1)))

What I need is, when I change month in the cell "D18" (which is referenced through the CONCATENATE formula), the "BI4" will change. If I increase the month, it will automatically change to "BJ4" and if I decrease the month, it will change to "BH4". Is that possible?

Answer 1

Try this formula

=SUM(Sheet2!$BE$4:BI$4)

When you drag this formula across(to right) it will become =SUM(Sheet2!$BE$4:BJ$4), =SUM(Sheet2!$BE$4:BK$4) and so on.

You are using Relative Reference in your formula. To get get desired result you should use Absolute Reference and Mixed Reference. See this for details on cell reference.

EDIT: As per comments

---

Use this formula

=SUM(INDIRECT("Sheet2!BE4:"&CONCATENATE(CHAR(66),CHAR(68+MID(TEXT($D$18,"DD/MM/YYYY"),5,1)))&"4"))

Note: INDIRECT is a volatile function. For details on volatile functions see this.

Answer 2

I'm going to make an assumption here - that you have your numerical figures on row 4, and in the row above you have the month starting date for that figure. Similar to the layout below.

Note: The month names in row 2 are real date values with a custom cell format of mmm - it says "Jan" but Excel reads it as 1st January 2017 (or day 42736 if you want to be picky).

With this data in place we can use formula to find the first day of the year and the required day as entered in cell D18.

The formula DATE(YEAR($D$18),1,1) will return 1st January for the date entered in cell D18.
To find this date in row 2 we can use MATCH to return the column number:
MATCH(<date formula>,$2:$2,0) or MATCH(DATE(YEAR($D$18),1,1),$2:$2,0).

Similarly we can use match to find the end date column number: MATCH($D$18,$2:$2,0).

Now we need to turn these column numbers into real cell references:
INDEX(<reference to whole row>,,<column in row to return>)
INDEX($4:$4,,<match formula to return column number>)
INDEX($4:$4,,MATCH(DATE(YEAR($D$18),1,1),$2:$2,0)) to return first cell reference.
INDEX($4:$4,,MATCH($D$18,$2:$2,0)) to return last cell reference.

Now all we need to do is stick these together inside a sum formula:
=SUM(<first cell reference>:<second cell reference>) or
=SUM(INDEX($4:$4,,MATCH(DATE(YEAR($D$18),1,1),$2:$2,0)):INDEX($4:$4,,MATCH($D$18,$2:$2,0)))

Of course, if you don't have the relevant date with your figures then this won't work....