# How to make text reference a number in Excel

1. ## How to make text reference a number in Excel

Hi all,
I am new to Excel. I am trying to create a spreadsheet for voltage drop. My question is how to make data from a drop down menu equal a number. Example: I have a drop down of all the different wire sizes. When someone picks a certain wire size, I want the data to be a certain number (hidden if possible). So all the user has to do is pick the correct drop down selection and it automatically references a number that I have in another cell. Is this possible?
Thank you,  Register To Reply

2. ## Re: How to make text reference a number in Excel

Welcome to the forum. Yes, it's possible - columns can be hidden. Can you manually mock up what you are envisaging, please, with a full list of codes and the equivalent numbers?

There are instructions at the top of the page explaining how to attach your sample workbook.  Register To Reply

3. ## Re: How to make text reference a number in Excel

Assume your code to lookup is in A1.
In a separate sheet, ie. Sheet2, in column A, place your codes you will look up.
In Sheet2, in column B, place the values to associate with the Codes in Column A
In Sheet1, in B1 type =VLOOKUP(A1,Sheet2!\$A\$2:\$B\$11,2,0) This assumes only 10 items

See the example in the attached.  Register To Reply

4. ## Re: How to make text reference a number in Excel

Here is an example worksheet. Let's look at H5-H9. I have created a few drop downs in these cells. What I would like happen, is when the user picks one of these numbers (say #4/0), I want it show as that, but in the formula I want the number in green (211600) to be what is used. Hopefully this makes sense?
Thank you!  Register To Reply

5. ## Re: How to make text reference a number in Excel

Insert a column after your drop-down list column, then add this formula to that column;

=IFERROR(VLOOKUP(H2,\$O\$2:\$P\$16,2,0),"")

Your calculation formula then becomes this:

=((D2*E2*F2*G2)/I2)/J2

Column I can be hidden if necessary.

You can't have the number show in the drop-down, however, without delving into VBA, which is probably unnecessary - KISS!  Register To Reply

6. ## Re: How to make text reference a number in Excel

Perfect! Thank you so much!  Register To Reply

7. ## Re: How to make text reference a number in Excel

Hi Spakkala,

In J2 use this formula
=((D2*E2*F2*G2)/(VLOOKUP(H2,\$N\$2:\$O\$16,2,FALSE))/I2)
and pull it down. See the attached for the full answer. Voltage Drop Calculator VLookup False.xlsx  Register To Reply

8. ## Re: How to make text reference a number in Excel

Doh!!! Why didn't I think of that? Nice one, Marvin.   Register To Reply

9. ## Re: How to make text reference a number in Excel

Thanks! There are a few ways of doing this it looks like! Appreciate it!  Register To Reply

10. ## Re: How to make text reference a number in Excel

Marvin's is the best of the three, in my view.  Register To Reply

11. ## Re: How to make text reference a number in Excel

Thank you!  Register To Reply