codes to display description

[Guest]

i have this excel document. there are item codes and the discription to the
items in the same row...items in one column and description in another column
.... for ex:

item code | description
row1 te35434 | nissan brake pad
row2 eos920 | poineer mp3 player


lets say i wanted to have these descriptions automatically display in
another worksheet when ever i select the item codes from a dropdown list. how
do i go about doing that..

any help would be greatly appreciated..
thanks in advance.

 

[Roger Govier]

Hi Ernie

On sheet2, assuming you have your dropdown in column A, and have
selected a part number in A2, enter the following formula in cell B2 and
copy down as far as required
=IF(A2="","",IF(ISERROR(VLOOKUP(A2,Sheet1!$A:$B,2,0)),"",VLOOKUP(A2,Sheet1!$A:$B,2,0))

Basically, the formula says, if A2 is empty, show nothing in B2, If
lookup of the item returns an error, show nothing, otherwise look for
the item shown in A2 in column A of Sheet1, and display the item on the
same line from column B of Sheet1.

 

[Guest]

it worked
thank you so much..

 

[Guest]

how do i create a drop down list on sheet2 from item code which is on sheet1?

 

[Guest]

it works just fine when the worksheets are named sheet1 and sheet2 and i am
having difficulties fixing it.
my sheets are named 'Tariff' and 'Data Input Sheet'.
i have the codes and description on the 'Tariff' worksheet. i need the
description to appear in 'Data Input Sheet'

it starts from row5 and column B in both worksheets

Example of what it looks like:

columnB | columnC
arrange the formula so it works with this setting for me please..

thanks you in Advance..

 

[Roger Govier]

Hi Ernie
Try

=IF(A2="","",IF(ISERROR(VLOOKUP(A2,Tariff!$A:$B,2,0)),"",VLOOKUP(A2,Tariff!$A:$B,2,0))



--
Regards

Roger Govier

 

[Guest]

hey Roger.. it works perfectly now.. thanks alot..
now i'm wondering how i'm gonna get this one to work.. heres the situation:
how do i make the appropriate ImportDuty to display with my item code?
the item code is in columnB while the ImportDuty is in columnI.
heres the example:

columnB | columnC | D | E | F | G | H |
columnI |
item code | description | | | | |
| importDuty|
row5 te35434 | nissan brake pad | | | | | |
20% |
row6 eos920 | poineer mp3 player | | | | | |
10% |

 

[Roger Govier]

Hi Ernie

Vlookup has 4 parts
VLOOKUP(A2,Tariff!$A:$B,2,0)
Item to lookup A2
Table to look in Tariff!$A:$B
Offset from found item 2
True or False (or 0 or 1) 0 (False)
False allows for the table to be unsorted, and ensures that an exact
match is found.

In the formula I gave you, you wanted the value from column B, or an
offset of 2.
Now you want values from column I, so first you must make the size of
the table bigger, and be
Tariff!$A:$I
and change the offset to 9.
You now know how to pick up any of the values from your table.

 

[Guest]

hi roger.. thanks alot.. it works perfect now.. i understand how the vlookup
formula works now so i wont be asking more questions about it..

but i have one problem that i think you might be able to help me with.. i
have figures in my columns that i want to change to percentage, when i change
the format to percentage it changes everything for example.. if i change the
format of a cell which contains '40', to percentage i get a result of
'4000%'. is there anyway to get around this? i need it to show 40% instead of
4000%.

 

[Roger Govier]

Hi Ernie

If you format a cell as percentage first, then you can type 40 and it
will show 40%.
If you type 40 in a cell, then format as percent it will show 4000%.
If you type 0.4, it will show 40%

If the cell is the result of the calculation, and it is showing as 40,
then you didn't divide your result by 100, which is what you would need
to do and then the result would be 0.4 not 40. Once you format as
percentage, you will then see your 40%.