For the purposes of the formula, I've got 2 columns on a sheet called "Mode".
Column A contains period numbers and column B contains passenger figures.
I've got a separate sheet which tracks the number of passengers depending on
the current period using a vlookup function as below:

=VLOOKUP($M$2,Mode!$A$2:$B$27,2)

This works fine when I'm only looking at 1 period. However, I also need to
calculate the cumulative totals (13 periods per year) - is it possible to
have the vlookup find the current period and then sum a number of rows above
this? To make it even more tricky, the number of cells to sum will change
along with the cumulative total (i.e. in period 2 there'll only be two
periods to count, in period 10 there'll be 10!).

Fingers crossed!

Sounds like you could best use a Pivot Table:

http://peltiertech.com/Excel/Pivots/pivotstart.htm

In article ,
Alex Finkel <Alex wrote:

For the purposes of the formula, I've got 2 columns on a sheet called "Mode".
Column A contains period numbers and column B contains passenger figures.
I've got a separate sheet which tracks the number of passengers depending on
the current period using a vlookup function as below:

=VLOOKUP($M$2,Mode!$A$2:$B$27,2)

This works fine when I'm only looking at 1 period. However, I also need to
calculate the cumulative totals (13 periods per year) - is it possible to
have the vlookup find the current period and then sum a number of rows above
this? To make it even more tricky, the number of cells to sum will change
along with the cumulative total (i.e. in period 2 there'll only be two
periods to count, in period 10 there'll be 10!).

Fingers crossed!