Show cross-referenced value from 2 combo boxes

[BruceS]

Hi!

I'm an Access programmer that has been asked to do a small project in Excel
and could use some assistance.

We have a spreadsheet containing "speeds" that are cross-referenced by
column headings of "material" and row headings of "size". They are wanting
to use a form with 2 combo boxes to select "material" and "size" and then
display the "speed". Only the form should be visible to prevent tampering
with the data behind it.

Could do this with a form and query in Access in about 60 seconds, but I've
never worked with Excel forms and controls. It must be Excel so that it can
be distributed to remote staff that can't connect to our DB and who don't
have Access.

Have tried using manual entries into cells with combinations of VLookup and
HLookup, but can't get results needed. Can someone point me to a resource
(sample code, tutorial, etc.) that will show me how to accomplish this?

Thanks,
Bruce

 

[joel]

I wouldn't do this with a Query. Instead I would use a recordset a pu
the results into a form in excel. The query would require a copy of th
data from the database to be put into an excel spreadsheet which yo
don't want.

The code of getting the recordset would be exactly the same code yo
would use in Access VBA. The only thing you would need to do is t
declare to references in the Excel VBA menu Tools - References


1) Microsoft Access 11.0 object library (or latest on your PC)
2) Microsoft ActiveX Data Object 2.8 library (or latest on your PC)


You can extract the data from the recordset and then place the into th
userform. You will only need help with creating the excel userfor
since the record ocde will be exactly like access

 

[Matthew Herbert]

Bruce,

I'm not exactly sure how your data is set up, but I've provided an example
below that may allow you to utilize native Excel before looking into creating
a User Form. You can protect cells within the worksheet to prevent users
from changing unwanted cells (i.e. the crosstab data).

All cell entries are simply entered into the spreadsheet.
B1: AAA
C1: BBB
D1: CCC

A2: Small
A3: Medium
A4: Large

B2: 1; C2: 4; D2: 7
B3: 2; C3: 5; D3: 8
B4: 3; C4: 6; D4: 9

Formula Data:
F1: Material
F2: Size
F3: Speed

User Input:
G1: CCC
G2: Large

Formula:
G3: =HLOOKUP(G1,$B$1:$D$4,MATCH(G2,$A$2:$A$4,0)+1,FALSE)

The user can change cells G1 and G2 to get the desired intersection. Let me
know if this is what you are looking to do; otherwise, I'll help get you on
the right track with the User Form and its Controls. (The above formula is
simply one way to do this. Other formulas that are related or can be used
are LOOKUP, VLOOKUP, and INDEX). The formula shoule return "9", i.e. the
intersection of CCC and Large.

Best,

Matthew Herbert

 

[BruceS]

Thanks for replying, Joel, but this has to be a totally "stand-alone" item.
No way to connect to the database here. The spreadsheet must run anywhere.
Bruce

 

[BruceS]

Matthew,

Looks like this will do it! I'll give it a shot.
Many thanks!!

Bruce

 

[BruceS]

Matthew,
Just wanted you to know it worked great! Thanks!!
Bruce