I'm using Excel 2000 and am trying to get a search/filter that will use two cell

values as the range for the criteria. For example, if I specify a cell with the

value of 100 and the next cell in the sorted range is 120, the criteria for the

search would be all matching items that range from 100 to 119. I've tried using

advanced filter with some IF(LEFT() and IF(RIGHT) but that is too restrictive and

would require some relatively complex formulas which I'm not sure I can do. I've

also searched for examples of VBA code that simulates advanced filter but have

not found any. Below is an example of some data and the result required. Any

help will be greatly appreciated.

Cell A20 used for first part of search

A19: 90

A20: 100

A21: 120

A21: 130

Other Range Data

89

93

99

102

105

108

111

119

121

129

Items Found:

102

105

108

111

119

You can use an advanced filter to extract the data.

You didn't specify how the starting value (100) is selected, so this

solution assumes you type a value onto the worksheet.

In cell G1, type 100

In cell H1, enter a formula to return the ending value:

=INDEX($A$2:$A$25,MATCH(G1,$A$2:$A$25,0)+1)

The other range data needs a heading cell (C1 in this example), and the

data starts in the row below (C2:C11)

Leave cell J1 blank, as the heading cell in the criteria range

In cell J2, enter: =AND(C2>=$G$1,C2<=$H$1)

Select a cell in the list in column C

Choose Data>Filter>Advanced Filter

For the Criteria Range, select J1:J2

Click OK

Or, you could add a formula beside the numbers in column C, then use an

AutoFilter to filter the list. For example, in cell D2, enter:

=AND(C2>=$G$1,C2<=$H$1)

Copy the formula down to the last row of data

Add a heading in cell D1

Select cell D1, and choose Data>Filter>AutoFilter

From the dropdown in cell D1, choose TRUE

--

Debra Dalgleish

Excel FAQ, Tips & Book List

http://www.yqcomputer.com/

You didn't specify how the starting value (100) is selected, so this

solution assumes you type a value onto the worksheet.

In cell G1, type 100

In cell H1, enter a formula to return the ending value:

=INDEX($A$2:$A$25,MATCH(G1,$A$2:$A$25,0)+1)

The other range data needs a heading cell (C1 in this example), and the

data starts in the row below (C2:C11)

Leave cell J1 blank, as the heading cell in the criteria range

In cell J2, enter: =AND(C2>=$G$1,C2<=$H$1)

Select a cell in the list in column C

Choose Data>Filter>Advanced Filter

For the Criteria Range, select J1:J2

Click OK

Or, you could add a formula beside the numbers in column C, then use an

AutoFilter to filter the list. For example, in cell D2, enter:

=AND(C2>=$G$1,C2<=$H$1)

Copy the formula down to the last row of data

Add a heading in cell D1

Select cell D1, and choose Data>Filter>AutoFilter

From the dropdown in cell D1, choose TRUE

--

Debra Dalgleish

Excel FAQ, Tips & Book List

http://www.yqcomputer.com/

Thank you Debra, this will work nicely. The first cell range is supplied by a

listbox control source cell (G1) and the second will be through a for each lookup

using Range("H1").Value = Range("A" & r.Row + 1).Value. The =AND is something I

hadn't come across yet in my two weeks of working with Excel 2000, but I like it

already. Thank you again.

cell

the

the

using

and

I've

have

Any

listbox control source cell (G1) and the second will be through a for each lookup

using Range("H1").Value = Range("A" & r.Row + 1).Value. The =AND is something I

hadn't come across yet in my two weeks of working with Excel 2000, but I like it

already. Thank you again.

cell

the

the

using

and

I've

have

Any

1. Macro to find the value of adjacent cell from a range of values

2. Find value in a range, then use value in adyacent cell

3. Check if cell value is found in a seperate range of values

4. Finding a value in a range where the value changes cells

5. Inputting cell value from source cell based on value in adjacent cell.

6. Comparing a cell value to a range value and display a matched value

7. Finding a named range based on cell value and copy/paste to same sheet?

8. formula- find min value in range, then average that value with one value on each side

9. Values contained in value list of second combobox based on value selected

10. compare multiple values in one cell to a range of values in multiple cells?

11. Formula needed: Return cell value in a range depending on other cell values

12. Values contained in value list of second combobox based on value selected in first combobox.

13. See if cell value appears within a value range given by another cell.

14. See if cell value appears within a value range given by another cell.

15. Checking if a cell value in one range is contained in a second range?

3 post • Page:**1** of **1**