Pre-defined Fields in Excel

[DeaconFrost]

In Excel 2007, we have a document for check requests. We have a field for account number, and the users pick from a drop down list of our existing account numbers. That part works very well, and is even set to give them an error message if anything other than the pre-defined fields is typed in, such as if they type abcde instead of 12345, etc.

Now, is it possible to somehow link this field to a second cell....so when the account number is chosen from the drop down list, a second, larger cell can display the name and description of the account? We have a second worksheet with all the account numbers listed down in the A column, if that matters. We'd like to type the name and description in the B column, so when someone selects 54321 from the drop down list, the name "Hardware Services" can be displayed next to it, in the next cell over.

Hopefully someone understands what I am asking, and has a suggestion. Thanks!

 

[pxc]

You can use VLOOKUP based on the check number cell, or write some VBA code in Worksheet_Change() to do the lookup.

 

[DeaconFrost]

I'm trying to do this now, but I'm not having much luck using the VLOOKUP function. I guess I need to read more on how to do this. I found a good link to explain how to use the VLOOKUP function, but I'm not seeing how to look through one column A (on a separate worksheet) and match A to some text in Column B (on that second worksheet).

VLOOKUP appears to need an exact data point in it already, such as the exact account number. Wait....can I use the cell (ie, D25) to fill in what I need VLOOKUP to use?

 

[pxc]

Wait....can I use the cell (ie, D25) to fill in what I need VLOOKUP to use?

Yep. Here's an example using 2 sheets: Sheet1 is where you are entering an account number into column A and Sheet2 holds the lookups in the A1:C5 range:

    A     B     C         A     B     C

1  ZZZ               1   VVV  info1  other1

2  XXX               2   WWW  info2  other2

3  YYY               3   XXX  info3  other3

4  VVV               4   YYY  info4  other4

5  WWW               5   ZZZ  info5  other5

      Sheet1               Sheet2

The formula you would use in Sheet1, column B (replace the first A1 only with A2, A3, etc for the other rows):
=VLOOKUP(A1,Sheet2!A1:C5,2,FALSE)

The formula you would use in Sheet1, column C (replace the first A1 only with A2, A3, etc for the other rows):
=VLOOKUP(A1,Sheet2!A1:C5,3,FALSE)

 

[DeaconFrost]

Thanks for all the time you put into it. After clicking through some links for VLOOKUP, I came across the LOOKUP function, and that ended up working perfectly. Thanks for steering me in the right direction. I was about to suggest doing this as a webpage!