Compare numbers to a tolerance range

**C2AUTOINC** · 11-25-2009, 12:16 PM

I'll try to explain this the best I can. I've got multiple pages of spread sheets with various standard washer sizes. They all have the upper tolerance and lower tolerance for the dimensions of the inner diameter, outter diameter and the thickness. Is there anyway that I can input the dimensions for a given washer to see if it falls into one of these standards without manually looking through the ten plus pages?

Thanks,
Chris

**Palmetto** · 11-25-2009, 12:55 PM

It would help if you upload a sample workbook that duplicates the sheet structure and includes some data (don't need all ten sheets).

It would be best, IMO, if you consolidated the data into one worksheet as this would vastly simplify the task.

**C2AUTOINC** · 11-25-2009, 01:26 PM

I've attached an example. In the top I would like to be able the dimensions I have for the part. There will be at least ten of these tables that I would like to be able to search through using this method, it would be very easy to put them all on one sheet as well. I just need a way for it to find which standard and what Nominal Size that the washer would fall into. I'm not sure if there's a way to display the text of the size and table name at the top or even highlighting the row that it's in would be sufficient.

Thanks for any help!

Here's the table incase the attachment doesn't work:

ID OD Thickness
Enter Value Enter Value Enter Value

Commercial Flat Washers - 316 Stainless
OD ID Thickness
Size Max Min Max Min Max Min
1/4 0.63 0.62 0.286 0.276 0.055 0.045
1/4 0.693 0.683 0.286 0.276 0.055 0.045
5/16 0.755 0.745 0.348 0.338 0.055 0.045
3/8 0.88 0.87 0.411 0.401 0.055 0.045
3/8 1.005 0.995 0.411 0.401 0.055 0.045
1/2 1.255 1.245 0.567 0.557 0.067 0.057

**Palmetto** · 11-25-2009, 11:19 PM

See if the attached is helpful. There are two approaches.

On Sheet1
1. Advanced Filter automated with VBA. A dynamic named range was created to use as the source for the filter.

VBA code for Advanced Filter

Sub Filter_Data()

    Application.ScreenUpdating = False
    
    Sheet2.Range("C9").CurrentRegion.ClearContents

    Sheet1.Range("Database").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheet2.Range _
        ("C1:I2"), CopyToRange:=Sheet2.Range("C9"), Unique:=False
    
    Application.ScreenUpdating = True

End Sub

On Sheet2
2. Match formulas and Conditional Formatting

=IF(NOT(ISNA(MATCH($B$2,B8:B13,0))),OFFSET(INDIRECT(ADDRESS(MATCH($B$2,B8:B13,0)+7,2)),0,-1,1,1),IF(NOT(ISNA(MATCH($B$2,C8:C13,0))),

**C2AUTOINC** · 11-30-2009, 10:22 AM

Thanks, this was very helpful and in the right direction. The first sheet made the most sense to me. However, the numbers are a range of possible sizes, if i try to put in a size between the two numbers it does not recognize it being in the range. For example if i put in 0.05 as a thickness it should recognize most of the thicknesses as correct. Also if there was a way to highlight an entire row when all three of the criteria were met that would be helpful.

Thanks,
Chris

**Palmetto** · 11-30-2009, 01:28 PM

Formulas on sheet-1 amended. After further review, it only seems necessary to search in the one column for values related to a specific tolerance input.

=IF(NOT(ISNA(MATCH($B$2,C8:C13,1))),OFFSET($A$7,MATCH($B$2,C8:C13,1),0,1,1),"No Match")

Also amended the CF formulas to work with the tolerance ranges to highlight the entire row if all inputs fall within tolerance.

On Sheet2: revised criteria formula to improve matching tolerances by padding the values. If there are matches, then the AF returns blanks, otherwise all records are returned.