+ 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
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,094

    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!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,273

    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

  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
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,094

    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
    15,250

    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
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,094

    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
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,094

    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. [SOLVED] 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