Complicated formula using Indirect, Index and Match, HELP!!!

[thesproing]

Ok,

Just recieved some great help for some peeps out there for helping me craft this formuale;

=INDIRECT("'"&$B$2-1&"-"&$B$2&"'!O"&ROW())

This helped me fill an array with data contained on another sheet depending on the value of a particular cell in the original....

This is the sheet using the formulae;

http://s188.photobucket.com/albums/z303/thesproing/?action=view&current=display.jpg

However, now what i need to do the same for the colum named "hours lost to sickness" which is calculated on another sheet. show below;

http://s188.photobucket.com/albums/z303/thesproing/?action=view&current=employeerecords.jpg

If we go back to the first table, this is the formula i am currently using to caluclate the value of the cells in colum N;



I knopw its a big ask, but is there any way of using a combination of IF, VLOOKUP and MATCH to simplify it all?

Something like....IF(B2=x,INDEX('Emplyee Records"!C5:Z34,MATCH("Employee Records""B2-1&"-"&B2",ROW())) ???

or maybe INDIRECT(INDEX('Emplyee Records"!C5:Z34,MATCH("Employee Records""B2-1&"-"&B2",ROW()))

I know im on the right track, i just dont have the experiance to get it right!!!

Any help, whatsoever would be amazing!!!!!!!

 

[thesproing]

oops, forgot the long winded formula for colum N;

=IF($B$2=$A$37,'Employee Records'!H7,IF($B$2=$A$38,'Employee Records'!I7,IF($B$2=Sickness!$A$39,'Employee Records'!J7,IF(Sickness!$B$2=Sickness!$A$40,'Employee Records'!K7,IF($B$2=$A$41,'Employee Records'!L7,0)))))

ridiculous i know, but the alternaitve seems too complex!!!!