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.
INDIRECT formula can help here. Try to use:
B2 and observe how results (sum) are changing, when you’re changing number in
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.