Adding up cells across worksheets

Try

=SUM(INDIRECT("January:"&[cell reference]&"!AG5"))
e.g.
=SUM(INDIRECT("January:"&A1&"!AG5"))

You can't do 3d ranges with Indirect.

I would recommend creating a helper column/row that pulls in all 12 cells information.
Say
A1 =January!AG5
A2 =February!AG5
A3 =March!AG5
etc...

Then put a month number in B1 to represent the employees Start Month (1 for Jan, 2 for Feb etc..)
Then do
=SUM(A1:INDEX(A1:A12,B1))

Or if you'd prefer to just type the month name into B1
=SUM(A1:INDEX(A1:A12,MONTH(B1&1)))

Yes - surprisingly not so straightforward.

An alternative set-up (though less efficient than Jonmo's) would be to first go to Name Manager and define SheetList as:

={"January","February","March","April","May","June","July","August","September","October","November","December"}

(Or whatever happen to be the sheet names in question.)

The required array formula** is then:

=SUM(INDIRECT("'"&INDEX(SheetList,N(IF(1,ROW(INDEX(A:A,1):INDEX(A:A,MATCH([cell reference],SheetList,0))))))&"'!AG5"))

where [cell reference] contains the desired end sheet name.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).