Is it possible to have a 'query'/filter from a master worksheet t.

[addie]

I have a master sheet with more than 500 records which I would like to sort
into groups. For each group I have to define a report. Therefore I am
thinking to build a query (filter like when you a running a mail merge in
Word) between the master sheet with the 500 records into several 'child'
sheets containing just one of the groups. This must be dynamic because the
grouping can change. And I don't like to do it allways manually, preferable
just a few mouse clicks....

 

[Arvi Laanemets]

Hi

Let's assume you have your data on sheet Master, data are in range A2:Y500,
with headers in row 1, and column C contains some group index. Further on
fly!

1. Create a report sheet, p.e. Report.
On report sheet, for some cell, p.e. B1, use Data.Validation.List, and enter
the list of all group indexes, or reference to such a list (use named range
when the list is situated on different sheet). You can now select a group
from drop-down list.

2. On Master sheet, add a column left to your data (column A, thus shifting
all data to right, so group indexes are now in column D)
A2=IF(OR($D2="",$D2<>Report!$B$1),"",COUNTIF($D$2:$D2,Report!$B$1))
and copy it down at least to end of your table. In column A are now numbered
rows where group index equals the one selected on report sheet. You can hide
the column A.

3. On Report sheet, add headers (without group) into some row, p.e. A4:X4.
Into cell A5 enter the formula:
=IF(OFFSET($B$1,MATCH(ROW()-4,$A$2:$A$1000,0),COLUMN()-1)="","",OFFSET($B$1,
MATCH(ROW()-4,$A$2:$A$1000,0),COLUMN()-1))
Copy the formula to range A5:C5
Change the formula in C5
=IF(OFFSET($B$1,MATCH(ROW()-4,$A$2:$A$1000,0),COLUMN())="","",OFFSET($B$1,MA
TCH(ROW()-4,$A$2:$A$1000,0),COLUMN()))
(it's because you doun't have group index column here)
Copy the formula in C5 to range C5:X5
Copy formulas in row 5 down for some reasonable number of rows.

In case the column Master!B:B is always filled for non-empty rows, you can
make formulas in columns B:X on Report sheet somewhat shorter - like:
=IF($A2="","",OFFSET($B$1,MATCH(ROW()-4,$A$2:$A$1000,0),COLUMN()))