Formula Help

[SUSANMLY]

Hi all, I know enough about excel to get by but I'm trying to create
form and need some formula help. If anyone can help I would appreciat
it. This is some what complicated so here goes.

I have 2 worksheets- the 1st is a list of item #'s in one column wit
their corresponding description in another column.

the second worksheet is a form and what I'm wanting to do is to be abl
to type in the item # from the 1st worksheet and the description wil
appear instead of the item #.

Is this possible? :confused

 

[JulieD]

Hi Susan

you said that you wanted to be "able to type in the item # from the 1st
worksheet and the description will
appear instead of the item #."

to do this requires using VBA in an worksheet_change event ... however, if
you wanted to type the item# in one cell and return the description in an
adjacent cell it is just a matter of using a VLOOKUP formula instead.

the VLOOKUP formula would be
=VLOOKUP(A1,Sheet1!$A$1:$B$100,2,0)
which says, lookup the value in A1 of this sheet, in the table of
information in Sheet1 range A1 to B100, when a match is found return the
information from the second column of the table, but only if it is an exact
match.

Hope this helps
Cheers
JulieD

 

[Jason Morin]

You need to use the VLOOKUP function. Examples of its use
can be found at:

http://www.contextures.com/xlFunctions02.html
http://www.techonthenet.com/excel/formulas/vlookup.htm
http://www.mvps.org/dmcritchie/excel/vlookup.htm

HTH
Jason
Atlanta, GA

 

[Guest]

SUSANMLY,

You will have to do this with VBA. Is this a VBA form your using? If so, put
a dropdown list on the form and in the change event for the list box, use the
vlookup function like this

YourVariable= Application.WorksheetFunction.VLookup(lookupValue, yourRange,
returncolumn)

where the yourRange variable is declared and set as follows:

dim yourRange as Range

Set yourRange= Worksheets(1).Range("AE2:AF5")

the range values in quotes are where your list is defined.

If your actually working in the worksheet and setting up your form there,
the approach would be similar, but the vlookup function will be in your cell
as follows:

=VLookup(lookup cell, AE2:AF5, 2)

This is assuming the value to be returned in the 2nd column of the range. If
you have more than 2 columns in your range, you will need substitute for the
correct column. This method does not get you away from the column where the
item number is diplayed like the VBA method will, but it accomplishes the
need to display the description.

Hope that helps!

Kevin