Hi again. Let's continue!

(You have a table on sheet MyTable in range A1:C100, where the first row
contains column headers (having headers is obligatory). Columns are Col1,
Col2 and Col3. You want to filter by Col1. You may have to add new records
to this table later, but you are sure the number of rows doesn't exceed
1000.)

2. Creating report sheet through formulas.

You have to add a new leftmost column into MyTable, where an unique
identificator is calculated for every row of table. And you craete a new
sheet, p.e. MyReport.

On sheet MyReport, you apply data validation list(s) to some cell(s), to
allow you to select filter condition(s). In our example, the list must
contain all possible values on column Col1 in MyTable. The best way is to
have such list on separate sheet, p.e. MyList. When you have header in
MyList!A1, and the list itself starts from cell MyList!A2, then define a
dynamic named range p.e. MyList
MyList=OFFSET(MyList!$A$1,1,,COUNTA(MyList!$A:$A)-1,1)
On sheet MyTable, select range A2:A1000, and apply to selected range data
validation list as
=MyList
(Applying data validation list to column Col1 prevents user entering
non-valid entries, and allows him to select entries from drop-down list)
On sheet MyReport, apply data validation list p.e. to cell MyReport!B1.
(When you want to filter by several conditions, p.e. by values for Col1 and
Col2, you have to create lists like this for every filtered column)
On sheet MyTable, add a new column to left of existing table - the new
column will be A:A, and your table is now in range B1:D1000. You can add a
header into cell A1, but later you hide this column anyway.
There are 2 possible ways to define an identificator. It may depend on
selected condition(s) on sheet MyReport, or not. An example for both:
A2=IF(OR(B2="";B2<MyReport!$B$1),"",B2 & COUNTIF($B$2:$B2,MyReport!$B$1))
, or
A2=IF(B2="","",B2 & COUNTIF($B$2:$B2,$B2))
(When you have several conditions, then when using first formula, you
replace COUNTIF with SUMPRODUCT, and in OR you check for all conditions.
When you use second formula, you have to compose an unique string form all
conditions, and again to use SUMPRODUCT to calculate an added counter - it
will be too complex to explain it here in detail)
Copy entered formula to range A2:A1000

On sheet MyReport, Into some row (p.e. row 3) enter column headings.
A3="Col2", B3="Col3"
When you used 1st formula as identificator, then:
A4=IF(ISERROR(VLOOKUP(ROW()-2,MyTable!$A$2:$D$1000,3,0)),"",VLOOKUP(ROW()-2,
MyTable!$A$2:$D$1000,3,0))
B4=IF(A4="","",VLOOKUP(ROW()-2,MyTable!$A$2:$D$1000,4,0))
When you used 2nd formula as identificator, then (with several conditions
you have to correct following formula):
A4=IF(ISERROR(VLOOKUP($B$1 &
ROW()-2,MyTable!$A$2:$D$1000,3,0)),"",VLOOKUP($B$1 &
ROW()-2,MyTable!$A$2:$D$1000,3,0))
B4=IF(A4="","",VLOOKUP($B$1 & ROW()-2,MyTable!$A$2:$D$1000,4,0))

Copy A4:B4 down for as much rows as you need.


Arvi Laanemets