Matching dats to a reference number

Hi, I have attached a spread sheet that has fields containing date, reference number and diagnosis. The reference number has a diagnosis referenced against it by a 1. What I would like to achieve is to have the diagnosis name in a column next to the reference number, and be able to identify how many diagnosis where noted per month, ie:

Month May Diagnosis Hodgkin's Number ?

I have created a list of the diagnosis on sheet 2 and would like to start inputting new data selecting from the list rather than have lots of column headings and having to scroll across. Tanks in advance.

I've made the changes that you required

Sheet5 has the fixed list of items for the drop-down when making new entries on Sheet2

Sheet2 has a simple pivot table applied to give you the summaries you requested

All existing data has been transferred to Sheet2, any entries with no diagnosis are displayed as No Diagnosis

just realised you need to re-apply the list data validation to the entire column C on Sheet2 for new entries

Thanks for your help.