Excel dropdown list with number and text

There are two methods;

Data validation drop down

There is a list option in Data validation. If this is combined with a VLOOKUP formula you would be able to convert the selected value into a number.

The steps in Excel 2010/2019 are:

  • Create your list with matching values.
  • On the Data tab choose Data Validation
  • The Data validation form will be displayed
  • Set the Allow dropdown to List
  • Set the Source range to the first part of your list
  • Click on OK (User messages can be added if required)

In a cell enter a formula like this

=VLOOKUP(A5,$E$5:$F$8,2,FALSE)

which will return the matching value from the second part of your list.

Form control drop down

Alternatively, Form controls can be placed on a worksheet. They can be linked to a range and return the position number of the selected value to a specific cell.

If you don’t see Developer tab in Excel ribbon, follow this link;

Show the Developer Tab

The steps in Excel 2010/2019 are:

  • Create your list of data in a worksheet
  • Click on the Developer tab and dropdown on the Insert option
  • In the Form section choose Combo box or List box
  • Use the mouse to draw the box on the worksheet
  • Right click on the box and select Format control
  • The Format control form will be displayed
  • Click on the Control tab
  • Set the Input range to your list of data
  • Set the Cell link range to the cell where you want the number of the selected item to appear
  • Click on OK

Server is ethernet connected on LAN, means exposed to the internet?

You may have an Internet connection, but you are almost certainly behind a NAT router, not directly connected to the Internet.  Normally, that NAT router is the only machine that faces the Internet, has a direct connection, and is under constant attack by numerous bots roaming the IP’s of the Internet. 

You only have a local IP for your local network.  Only the router has your true IP that is seen on the Internet.  When your browser or NTP service (or other Internet need you may have) needs to see something on the Internet, it makes a connection to an Internet server, and your router notes that connection and allows that server to respond, using the associated ports of your connection.  The router will route those responses back to your machine, and not any other. 

The outside bots and servers cannot attack or connect to your machine, because they can’t even see it, and they don’t know your local IP.  The only contact that outside machines can have with your machine is strictly through connections your machine initiates first, through your router.

Now if you *did* want to put your server directly on the Internet, most routers have a setting where they can put any machine into a ‘DMZ’, a special unprotected zone, which means the Internet is directly connected to any machine you choose!  And the router won’t block any Internet traffic then, but allow all of it to come through to you. 

I would strongly advise you to first disconnect ALL of your drives, and backup your boot drive, because you will be very rapidly attacked!  Never use the DMZ unless you have a lot of security experience!