Hello,

I have a spreadsheet in which each worksheet is a different month of the year (January thru December). The layout of each worksheet is identical, but the data entered will be different. Basically, I am counting monthly sales contracts and their values (I work for a residential builder) and tabulating the month the contracts were written, the month they will close, the value of those contracts and the communities in which that sales occured. Each monthly worksheet has a tabulation sheet that counted/totaled the above mentioned items. It would be simple if each of those monthly tabulation sheets only counted items within that worksheet.

The "rub", however, is when the date in which the contract was written differs from that of the month in which it closes. For example, I could have a contract dated 6/8/08, but scheduled to close on 7/12/08. The June tabulation sheet would show "1" for contracts written, but "0" for contracts closed. However, the July tabulation sheet needs to show "1" for contracts closed.

It is very possible there is a simple way to accomplish this, but I am not aware of it. I created a form of this spreadsheet a couple of years, but it was a monster and prone to error. It was filled with a ridiculous amount of COUNTIF and IF/THEN statements. I have simplified my current attempt by implementing "array formulas", which greatly simplified the structure. However, it still does not address the issue of data "transferring" from one worksheet to another. I was hoping that I could create a range that was not limited to one worksheet. That way, instead of searching one column on one worksheet, I could write an array formula that covers a range of cells (A1:A60 on each worksheets) and SUM it if it meets certain criteria. However, I have not had any luck with the 3-D references. I created one by following the steps described under the Help! menu, but when I tried to locate it in the "Name Box" it is not there.

Any help or suggestions would be greatly appreciated!!!

Thanks,

Rob