Indirect + Lookup last value

Dear all,

I am using a formula that indexes and matches from another sheet (2006). It retrieves all the latest values.

=INDEX('2006'!B9:B375,MATCH(9.99999999999999E+307,'2006'!B9:B375))

However, I have created lots of different sheets with years and the only way I have found to update this formula is to manually change it from, say, '2007','2008' etc. I want this to be dependant on the year, however, e.g. YEAR().

I can't seem to add a reference to a cell with a year though. Would anyone be kind enough to give me some advice?

Regards,
Robin

Please read forum rules below and then change your title

VBA Noob