macro/ lookup to enter codenumber for when text selected

[rajesh84]

Hi all,
Back after a long time.

I am trying to setup a drop down list in a cell (with about 25 entries in the look up) and when i select one of them I would like its corresponding code (reference) number to appear in a subsequest cell. For eg- if i select "asfdgehr" from the drop down list, the ref number 1234 should automatically come into another cell.At present i enter each text string,then i look up the corresponding number from a pdf file and then type it in. as you can imagine,pretty hard work !

thanks for your help.

 

[mmmarks]

Paste this formula in B1 =IF(A1="","",VLOOKUP(A1,Sheet2!A1:B4,2,0))
instead of VLOOKUP(A1,Sheet2!A1:B4,2,0)

 

[rajesh84]

Tried it.

2 problems.

A1, A2 etc- all come up with just "names" on the dropdown list

And an error message "a value is not available for the formula to function"

any suggestions?

thanks

 

[rajesh84]

sorry. still the same error.

In sheet 2, my data is a1 to b4, isn't it? I then select a1-4 and name the range.- I also tried a1-b4 as a range but that did not work either.

 

[mmmarks]

Ok.
Try in this way...
First select the range which is exist in A column in Sheet2 and named it as NAMES. (only one column) not entire table.

Now , Select A1:A10 column in Sheet1 then Go to-->Data Validation.-->then List---->Then in Source, type =NAMES and press enter.

Then You will get Drop down Symbol at Cell level.

 

[rajesh84]

Thanks. that works- to some extent.

The value in b1 comes up right but the values in b2 ,b3 etc comes up with the wrong value (ie, it comes up with the same value as in B1,rather than the value that is supposed to come from b2)

so i tried changing it to =IF(A1="","",VLOOKUP(A1:B4,Sheet2!A1:B4,2,0)) but that came up with a "circular reference error".

Is there a way of telling excel that for A2,it should look at the value in B2 and so on down the column ?

thanks

 

[rajesh84]

works now !

but I have to change each cell =IF(A3="","",VLOOKUP(A3,Sheet2!A1:B4,2,0)) etc

so i copied the formula and dragged down the column to paste it and it is working.

is that the only way to do it or is there something simpler ?

thanks

 

[mmmarks]

To get accurate result ... fix the table by the symbol $ in range of the table..

=IF(A1="","",VLOOKUP(A1,Sheet2!$A$1:$B$4,2,0))

Now drag the formula to down

 

[rajesh84]

Thanks very much mmmarks.

works very well.

much appreciated.