Category Archives: excel

How to create a drop down menu in Excel

This short tutorial will show you how to create a drop down menu in Excel. The Drop down menu will allow you to ensure that only the correct data gets entered into a field.

In this example we want to ensure that the end user can only enter the following information into a selection of cells

  1. On Time
  2. Late
  3. Early
  4. Cancelled.

Step 1

Enter the the options for your drop down menu in cells in your workbook. in Our case we entered them in column M.

Step 2 

Now highlight the cells you want to have the drop down menu in and click Data.

Step 3

Click data Validation and data validation again

Step 4

In the box that appears select List

Step 5

Click into the Source menu box and then highlight the values that you want to be in your drop down menu, in our case M2 to M5. Then click ok

Step 6

Your Drop down options should now appear:

A full video tutorial ca be found here:

Producing HTML links from a CSV Using Python

Recently in work I had to generate a long list of html links from two columns that I had extracted from a MySQL database to a .csv file. The example below shows what steps are needed to generate html urls from a csv file that has the text of the link in one column and the url in the other. The columns that I extracted from the database into a .csv file looked like the following:

 

Master of Arts in Learning https://testurl.com/courseid=123
Postgraduate Diploma in Arts https://testurl.com/courseid=127
Postgraduate Diploma in Computing https://testurl.com/courseid=128

 

In the file, there were roughly 400 rows. I needed to add the name of the courses with their link to a website as a list. The list created would allow students to browse for their course and follow the correct link to their course.

 

I had access to the html so I needed to add roughly 400 html links. To do this manually would take hours. So I decided I would need a script to produce the html. The script I came up with looks like the below:

 

import csv, sys
sys.stdout = open(‘links.txt’ , ‘w’)
for row in csv.reader(open(‘courses.csv’),delimiter=”,”):
print (”'<a href =\””’+ row[1] + ”'”>”’ + row[0] + ‘ </a> </br>’)
sys.stdout.close()

 

  1. Line one in the script; Import csv, sys. imports modules that I needed. The csv module facilitates the opening of .csv files while the sys module allows the redirection of output to a file.
  2. The second line; sys.stdout = open(‘links.txt’ , ‘w’) Creates and opens a file in write mode called links.txt.
  3. Line three, is a for loop, that opens the courses.csv file and goes through it line by line.
  4. As the loop goes through the file line by line, it prints  <a href =” and then the contents of the second column (The URL). Then it prints the closing bracket for the tag. Next we print what is in column 1, the close </a> tag and finally a </br> tag which will insure that each link exists on a new line on our webpage.

 

When we run our script the output produced to the links.txt looks like:

 

<a href =”https://testurl.com/courseid=123″>  Master of Arts in Learning</a></br>
<a href =” https://testurl.com/courseid=127″> Post Graduate Diploma in Arts</a></br>
<a href =”https://testurl.com/courseid=128″> Post Graduate Diploma in Computing</a></br>

 

This code can then be copied to our website which will then display a list of links that users can select.