Dropdown list selection from column 1 but puts in value from column 2

Hello,

Here is what I am trying to do:

Look up sheet

Column 1
External Mandate
Internal Mandate
Not Mandated

Column 2
100
10
1

I have a dropdown menu setup on data sheet with the following:

Column 1
Drop down menu

Column 2
VLOOKUP that bring in the data from column 2 based on the selection of column 1 from the look up sheet.

However, this is two columns. I want it to be one column. User selects from drop down menu, "External Mandate", I would like the same cell to show the 100 instead of the name.

How would I go about doing this?

Please refer to attached file.

Make a named range called "rangeVal" for Sheet2!A1:B7

Code on Sheet1 :

Please Login or Register  to view this content.

Code on Module1 :

Please Login or Register  to view this content.

You are welcome, and thanks for the rep.points.
Don't forget to mark this thread as solved :
please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

ok, the column update did not work. So I am still stuck with applying the above to entire column not just one cell.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

I think there is better way to achieve your goal. Instead of replacing validation string with numbers (which make it difficult to verify what you have already input on the cell), it is better using an UDF that sum the row's values based on vlookup values, this way the validation string is unchanged.

-------------------------------

For learning purpose, here is the way using original method (replace validation string with associated number) :
1) Create named range as much as validation data (in this case : vldB, vldC, vldE, vldF, vldG, vldH, vldI, vldJ)
2) Assign this named range as validation list criteria for each cell, for example for cells on column B, the validation list formula is =INDEX(vldB,0,1)
3) Put this code on Sheet "Project" :

Please Login or Register  to view this content.

4) Put this code on Module1 :

Please Login or Register  to view this content.

-------------------------------

Now, using UDF method :
1 and 2) Same as first and second steps above
3) Put this code on Module1 :

Please Login or Register  to view this content.

4) Put this formula on cell K7 (and copied down) :

Formula:

Please Login or Register  to view this content.

You are welcome, glad to help.


Regards