3-D Reference

[slovenc0417]

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

[EdMac]

Hi Rob,

Can you post a zipped example of your sheet so we can have a look? Put some words of explanation on it so we can see what you are trying to achieve.

regards

[slovenc0417]

Hello EdMac,

I have attached a watered down version of my spreadsheet showing the months of January through March. The example I have attached has data entered in the January and February worksheets. You will see that there are two contracts for the ABL community in January and one for Februray. I need the "Community Analysis" tables at the bottom of each worksheet to total the contracts correctly.

Essentially, the ABL community for the January "Community Analysis" should show "3" contracts and "2" closings (Note that although lot 003 is entered in the February worksheet, the actual contract was written in January and must therefore be included in the "contracts written in January cell"). Furthermore, the ABL community for the February "Community Analysis" should show "0" contracts, but "1" closing.

Some of the other cells of this worksheet will work in a similar fashion, but if I can get this one example to work I am sure I can take it from there. There is also some conditional formatting in the Excel that you do need to worry about.

Thanks,

Rob

[EdMac]

Hi Rob,

SUMPRODUCT is probably the best way to do this but it doesn't work in 3d arrays as far as I'm aware.

If the figs are only going to be in 2 sheets then this in F14 does the trick

Please Login or Register  to view this content.

and can be copied down.

Post back if you need more.

Regards

[slovenc0417]

Ed,

I think this will work for me!!! Although I will have to extend the formula to cover each month of the year, this will still be much better than what I have. I won't know for sure until I put it all in... Thanks!

Can you briefly explain to me the function of "--" in the formula?

Thanks,

Rob

[EdMac]

the -- is called a double unuary and forces the result to evaluate numerically.

For information on SUMPRODUCT see this site, it's very helpful.


http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Regards