A list of homeworks

1) In this other book, available here, on page 104, perform exercises 41 through 44, as well as 49. See if you can do exercise #50. Throughout, gives names to the input cells. Hint for #50: The most straightforward way to approach this is with a text formula, from chapter 5 in the book. Sign the Excel spreadsheet.

Submit it through Blackboard or, barring that, email it to me.

2) In the online book, page 289, questions 23 - 27.

3) Implement the database as described in the video here:

4) Modify earlier example using match and offset to allow a primary key, rather than just relying on Order.

5) Homework:
a) Do relational database with MATCH and INDEX.
b) Given a field containing someone's firstname middlename lastname, extract the middle name.
c) Let us say that a sentence can contain up to 5 words. Combining IF with SEARCH, write a formula which will tell you how many words exist.
d) Write a UDF which returns X squared.

6)
a) Generate the ASCII chart using the code function.
b) Using the pattern in the book as a template, write a formula which will make a letter uppercase.
c) Write a statement using the AND function and CODE function which tells whether a character is a lowercase character.

7)
Ch 17, do the Progress towards a goal, and the Gantt chart.

Final Exam

Write the answers in your booklets.
http://docs.google.com/Doc?id=ajbqhgmq9qdz_111gsk2rhnn

Time and Place for the Final

B131 (the regular lab)
time: 6:15-8:15 PM, today

Dec 22
6:15 - 8:15
Room TBA, but we should have a lab

Full takehome is now up

Reload the page, to check it out.

Link to Midterm #2

http://docs.google.com/Doc?id=ajbqhgmq9qdz_128c4d3tvpk

work in progress, first half

Link to Prentice Hall to get downloads

http://prenhall.com/grauer/

Connecting to an Access Database from Excel Using VBA

Sub GetMyData()
Dim cn As New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\josh\Investment.mdb;User id=admin;"
cn.Open

Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = cn
'cmd.CommandText = "Delete * From Clients Where FirstName='" & Range("E2") & "'"
cmd.CommandText = "Update Clients Set FirstName='John' Where SSN='111111111'"

cmd.Execute
Exit Sub

Dim rs As ADODB.Recordset
Set rs = cmd.Execute
Dim I As Integer
I = 1
While Not rs.EOF
Range("A" & I).FormulaR1C1 = rs("FirstName")
Range("B" & I).FormulaR1C1 = rs("LastName")
I = I + 1
rs.MoveNext
Wend
End Sub