+ Reply to Thread
Results 1 to 11 of 11

How to make text reference a number in Excel

  1. #1
    Registered User
    Join Date
    07-31-2020
    Location
    Hancock, Michigan
    MS-Off Ver
    2010
    Posts
    9

    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,

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    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.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    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.
    Attached Files Attached Files
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  4. #4
    Registered User
    Join Date
    07-31-2020
    Location
    Hancock, Michigan
    MS-Off Ver
    2010
    Posts
    9

    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!
    Attached Files Attached Files

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    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!
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    07-31-2020
    Location
    Hancock, Michigan
    MS-Off Ver
    2010
    Posts
    9

    Re: How to make text reference a number in Excel

    Perfect! Thank you so much!

  7. #7
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    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
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: How to make text reference a number in Excel

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

  9. #9
    Registered User
    Join Date
    07-31-2020
    Location
    Hancock, Michigan
    MS-Off Ver
    2010
    Posts
    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!

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: How to make text reference a number in Excel

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

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  11. #11
    Registered User
    Join Date
    07-31-2020
    Location
    Hancock, Michigan
    MS-Off Ver
    2010
    Posts
    9

    Re: How to make text reference a number in Excel

    Thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need to make excel reference a table
    By JustinC7 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-25-2015, 06:02 PM
  2. [SOLVED] Make text to number - Macro
    By geliedee in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 02-26-2015, 07:11 AM
  3. [SOLVED] need to make text into number format
    By stvbrown2184 in forum Excel General
    Replies: 3
    Last Post: 05-20-2014, 09:42 AM
  4. [SOLVED] Make Excel count a text value and convert it to a number in another sheet
    By maximelling in forum Excel General
    Replies: 3
    Last Post: 05-02-2012, 05:42 AM
  5. Excel 2007 : How to make text represent a number.
    By jimleslie77 in forum Excel General
    Replies: 1
    Last Post: 02-02-2009, 05:17 PM
  6. make it possible to get to the function reference for excel when
    By an unhappy excel user in forum Excel General
    Replies: 1
    Last Post: 04-20-2006, 07:15 PM
  7. how to make number as text and left justified?
    By mango in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-21-2005, 05:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1