Excel Form and Data Retrieval
 

I'm trying to create a rather complicated form and I'm not sure where
to begin. I am a research technician for a paper mill and often times
I am given competitive samples to test and compare against grades of
paper produced at my mill. I usually test the competitive sample for
various properties and then browse through hundreds of grades to find
the closest match. What I'm trying to do it create a form that will
do some of that for me. The specifications for each of our production
grades are maintained in excel workbooks. Each workbook contains
dozens of grades on separate worksheets. Each worksheet looks
something like this:

Test Name | Freq | Min | Low | Target | High | Max|
Weight 1 50 52 54 56 58
Caliper 2 0.5 0.6 0.7 0.8 0.9
Tensile 1 85% 86% 87% 88% 89%

There are generally about 20 tests per grade. I want to design a form
that I can enter the competitive data into and it will scan the
workbook for the closest matches based on a percentage of test
properties that fall within a certain grade specification.

My second option is to create a form that will do the same thing, but
only look at individual worksheets so I would need to run the macro
each time I opened a new worksheet. To complicate matters even
further, each grade has it's own battery of tests so, while test
names are always identical from worksheet to worksheet, the ones that
are not needed are generally deleted. Therefore, for the data
retrieval, I would need some kind of lookup function because
"Tensile" on Sheet A may be found on line 20 but on Sheet B it
might be found on line 24. The name itself will always remain
constant.

I have no choice but to use excel for the grade specifications because
that's what my management has decided. I realize this is very
complicated. I don't expect detailed answers, but if I'm pointed
in the right direction, I'm pretty resourceful. Thanks in advance.

-- John