Show cross-referenced value from 2 combo boxes

  • Thread starter Thread starter BruceS
  • Start date Start date
B

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
 
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
 
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
 
Back
Top