limit cell value by date
 

I have a workbook with 2 worksheets (let's sheet1 and sheet2). In sheet 1 I
am entering data, in sheet 2 I wanna have consolidated data from sheet 1.
'Sheet1'!D4=TODAY()
'Sheet1'!D12={SUM(IF($D$20:$D$54="XTYPE",$H$20:$H$ 54,0))}
In sheet2 I have twelve rows responding to each month of year.

So, I want to make formula with following conditions:
if 'Sheet1'!D4=TODAY() is equal to last day of the month (let's say
30-Nov-2005) then 'Sheet2'!D11 is equal to 'Sheet1'!D12.
And (!!!) maximum value of 'Sheet2'!D11 must be value of 'Sheet1'!D12
that was produced on last day of month.

Is it possible to make formula for this? If it must be VBA script
can you post it please. or email it to
Thank you.