ACTIVE filtering
 

From a table of information I would like to display a full row or rows of
data based on a set of lookup criteria, if a1:a20 has a value = to 20
+ or- 10% and the same row has a value of 50 + or - 10% b1:b20

The values will always change, therefore I would like the formula to look at
a cell where I can input the required value and automatically filter when
the value is changed

To help you understand the spreadsheet is a simple list of equipment model
numbers with capacities fan dimensions and noise data I would like to input 3
Selection criteria and be presented with each row that complies with in 10%

selection criteria and be presented with each row that complies with in 10%

Thanks for any help you can provide

ACTIVE filtering
 

One possibility is illustrated in this sample construct:
http://www.savefile.com/files/601484
Display lines satisfying multicriteria tolerances.xls

Assume source table in sheet: X, cols A to D, data from row1 down
Cols A and B are the key cols housing numeric values

In another sheet: Y,

Base inputs for X's cols A and B will be made in A2:A3

Put in B2: =A2*0.9
Put in C2: =A2*1.1
Select B2:C2, copy down to C3. B2:C3 computes the lower and upper tolerance
limits (+/- 10%) for the 2 base inputs.

Put in E1:
=IF(OR($A$2="",$A$3=""),"",IF(AND(X!A1=$B$2,X!A1< =$C$2,X!B1=$B$3,X!B1<=$C$3),ROW(),""))

Put in F1:
=IF(ROW()COUNT($E:$E),"",INDEX(X!A:A,SMALL($E:$E, ROW())))
Copy F1 to I1. Select E1:I1, copy down to cover the max expected extent of
data in X, say down to I100. Hide away col E. Cols F to I will return the
required results, ie only those lines from X satisfying the input criteria's
tolerances for col A and col B, with all results neatly bunched at the top.
Adapt and extend to suit.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Andy K" wrote:
From a table of information I would like to display a full row or rows of
data based on a set of lookup criteria, if a1:a20 has a value = to 20
+ or- 10% and the same row has a value of 50 + or - 10% b1:b20

The values will always change, therefore I would like the formula to look at
a cell where I can input the required value and automatically filter when
the value is changed

To help you understand the spreadsheet is a simple list of equipment model
numbers with capacities fan dimensions and noise data I would like to input 3
Selection criteria and be presented with each row that complies within 10%

Thanks for any help you can provide

ACTIVE filtering
 

Hi,

Copy all the columns of a single spread sheet, and paste column heading in a
new row in the same excel spead sheet.

Now I wiil be using the Advance filtering techniques so that all the column
information are retrieved even if the typed cells are misspelled, which
normally is not the case when you do a simple filtering technique. The
filtered values may not be correct.

To retieve the values;

1. Select all records and the column headings and define a name for the
range List. This is the List range name.
2. Select all column headings and paste in a seperate row anywhere in the
spreadsheet.
2. Select the pasted column heading along with an empty row and define a
name. This is the criteria range name.
3. Define a criteria on the second row for all columns, if necessary. For
example S* will display all details specific to "S'.
4. Click Data- Filter- Advanced Filter
5. Type the List name
6. Type the Criteria Name
7. Click copy to another location option - Mandatory
8. Click Copy to
9. Click the cell below the criteria range

All data will be displayed specific to the query you requested. This is very
usefull if the spreadsheet data are mistyped or mis-spelled. Now you have
done a database funtion on your excle spread sheet.

Challa Prabhu

"Andy K" wrote:

From a table of information I would like to display a full row or rows of
data based on a set of lookup criteria, if a1:a20 has a value = to 20
+ or- 10% and the same row has a value of 50 + or - 10% b1:b20

The values will always change, therefore I would like the formula to look at
a cell where I can input the required value and automatically filter when
the value is changed

To help you understand the spreadsheet is a simple list of equipment model
numbers with capacities fan dimensions and noise data I would like to input 3
Selection criteria and be presented with each row that complies with in 10%

selection criteria and be presented with each row that complies with in 10%

Thanks for any help you can provide

ACTIVE filtering
 

Assuming that you have copied all the column heading to a different location
in the same spread sheet, you know the column heading now, save the excel
sheet.

Now I wiil be using the Advance filtering techniques so that all the column
information are retrieved even if the typed cells are misspelled, which
normally is not the case when you do a simple filtering technique. The
filtered values may not be correct.

To retieve the values;

1. Select all records and the column headings and define a name for the
range List. This is the List range name.
2. Select all column headings and paste in a seperate row anywhere in the
spreadsheet.
2. Select the pasted column heading along with an empty row and define a
name. This is the criteria range name.
3. Define a criteria on the second row for all columns, if necessary. For
example S* will display all details specific to "S'.
4. Click Data- Filter- Advanced Filter
5. Type the List name
6. Type the Criteria Name
7. Click copy to another location option - Mandatory
8. Click Copy to
9. Click the cell below the criteria range

All data will be displayed specific to the query you requested. This is very
usefull if the spreadsheet data are mistyped or mis-spelled. Now you have
done a database funtion on your excle spread sheet.

Challa Prabhu



"Andy K" wrote:

From a table of information I would like to display a full row or rows of
data based on a set of lookup criteria, if a1:a20 has a value = to 20
+ or- 10% and the same row has a value of 50 + or - 10% b1:b20

The values will always change, therefore I would like the formula to look at
a cell where I can input the required value and automatically filter when
the value is changed

To help you understand the spreadsheet is a simple list of equipment model
numbers with capacities fan dimensions and noise data I would like to input 3
Selection criteria and be presented with each row that complies with in 10%

selection criteria and be presented with each row that complies with in 10%

Thanks for any help you can provide