collecting rows into an array for fasten up array formulas, is it possible?

[naxa]

Hi all,

I have a table with 250 rows and columns A-AD. Each row has different types of information about a "bank paper". The table must be unsorted due to several reasons. I have to summarize/count the papers type by type. For example coloumn AD has the information if the paper's type is "NEW" or "DELETED". Also AC has the information if it is paper "Type 1" or paper "Type 2" etc.

I have to do many counts like this: count how many NEW, Type 1 paper exist in the table. I do this with array formulas since there are many aspects I should "filter", but it is too slow.

My question is: is it possible to collect the correct rows, for example all "NEW" rows into an array somehow? I want this to fasten up the search, since now the array formulas for finding NEW & Type 1 and NEW & Type 2 searches two times for NEW, what would not be necessary if I could collect all NEW rows somehow into someting like an "array-cell".

(I tried to do it with OFFSET, MATCH, INDIRECT and Ctrl-Shift-Enter with no luck, also thought about INDEX but I think these formulas are not for this.)

I have Excel 2003 (and two days ).

[arthurbr]

Hi and welcome to the board

Have you had a look at Pivot Tables?

[naxa]

Hi and thank you!

To be honest, I am not too familiar with Pivot Tables. However, I tried to use them, but I chosed not to use them becouse it is not guaranteed that the data source's size is constant.

I also cannot use a named range, becouse I have to create a template, and the user will create worksheets from the template by copying them and renaming it corresponding to the current month. (I do not know how can I create a dynamic named range what also renames itself, without VB code, what I was told not to use.)

But since I am not into Pivot Tables, I am unsure if I am missing the easier way here. I also have to create datas for the whole year from the individual worksheets. (This is now done by copying the final data with formulas.)

[Richard Schollar]

Hi

Great link on pivot tables here:

http://peltiertech.com/Excel/Pivots/pivottables.htm

The dynamic named range would exist on the data sheet only so I don't see a problem with using one for the Pivot Table data source. Since you are using xl2003 you might also consider setting the data source as a List (which will expand and contract as data is added).

Richard

[naxa]

Thank you for your help, I will give it a try then!

For my personal interest, is there such a thing what I tried to use originally?

[Richard Schollar]

I don't think so I'm afraid - you will always end up evaluating the NEW rows at least twice if you want counts of Type1 and Type2 in separate cells if you use formulas.

[naxa]

Thank you very much for help and information! If that's the case, then I think this thread could be considered as solved with the general solution that one should use Pivot Tables with list as data source. (I don't know how to make a Thread "Solved", so I hope that this is not my duty. )