Conditional ranges in Excel using formulas

This might be obvious for many, but since I found nothing doing quick Google, I wrote it down here for my future reference.

Suppose you have a column containing 60 entries — A1:A60. Let’s say that these are your company projected income for the following five years. In B1 you have a number 1-5. And according to that number, you want to display in B2 a sum from 1, 2, 3, 4 or 5 following years.

The INDIRECT formula can help here. Try to use:

[code language=”shell”]
=(SUM(INDIRECT("A1:A"&(B1*12))))
[/code]

in B2 and observe how results (sum) are changing, when you’re changing number in B1.

Keep in mind, that whatever you put into “fixed” part od indirect range addressing(“A1:A” in this example) is NOT being updated automatically by Excel (however, second part — B1*12 here — yes). You have to updated manually, if your ranges changes.