Project 3 – Week 5 (Day 2), – What I Did Today – Coding and Layout

I mostly spent the day trying to configure VBA and finish up the other sheets. I can split what I did today into 3 parts. Here’s what I did:

  • Finishing the Finance Sheet (Costs) – Remember that little area that was covered up in my finance page? This is it. This was quite confusing, and at first I wasn’t really sure how to go about itm but eventually, I was able to figure most of it out. Here’s an annotated screen shot to show the different difficulties:

Costs

The tutor part was the most difficult(once again). Since it was per day, I had to get it to to refer to the bookings made, and it the class were to run, it would count the cell. Logically, I would have gone with a COUNTIF function. I was thinking of how to go about it, when I thought of another way. I could have made a COUNTIF function to count the 3 cells again if it said :Tutor Required,” but then that would have just complicated things further and I would encounter the same problem I had in the previous lesson (the one where I tried to make the tutor required, not required things show up). Instead, I used this way:

First, I created a COUNTIF function for each tutor required cell.

Tutor Cells

It may seem out of the way, but in the long run it’s much easier. I made them each have a COUNTIF function individually, so that there would be a corresponding cell that would have a number 1 if they were counted. Like this:

COUNTIFS for Tutors

Then, I added this cell to my finance tables and used a SUM function to count the cells:

SUM Function

That way, I could easily calculate the costs of Tutor’s per week. I just simply added in a PRODUCT function into the costs table (the number of tutors required multiplied by the costs of a tutor per day).

  • Navigation – I spent quite a lot of time on this one. This is basically a picklist and a button with a macro. It required a bit of VBA knowledge to do. It looks different for almost every sheet, but it pretty much looks like this:

Navigation

It’s basically two cells and a button. The difficult was making the macro for the button. There was a lot of thing needed to be added there. I admit, someone did help me out a bit. Here’s what the code looks like:

Coding of the Navigation

This is quite similar to my Userform, except there is now an IF. Basically, what this coding means, is that if I press the go button, the system will run a check. If the cell (A12) has the word “Welcome” in it, then it will change to the sheet named “Welcome.” If the cell (A12) does not have the words “Welcome”, it will check for the words “Bookings”, and if it has that, it will go onto the sheet named “Bookings”. If not, it will check the next IF statement, and so on and so forth until it finds a word that is stated in the coding.

I have applied this to all of my sheets (but the coding is slightly different, because not all of them have the same cells. Some of them are in different places).

  • Welcome Page – I also started working on my Welcome page. It looks like this right now:

The Current Welcome Page

You can clearly see the Navigation area right now. You can also see a few tables.

Here’s the formula I used:

=20-Bookings!AD4

basically, I just made it like this: the number 20 minus the cell with the total number of bookings for the day (which is in my booking sheet)

The cells with the total no. of bookings for the dayk

It’s a lot easier that way for me.

I’m not quite sure what else to add to the Welcome page.

I realize that my project is getting to be more and more similar to the example I was given at the start of the project. I hoping it’s the learning factor that counts, and that the subtle differences in the way I made it will be acceptable.This whole thing has to be up and running by next week, so I’m going to have to start finalizing. Although, I don’t think I’ll be able to finish. In all likelihood, a lot of things in my project still won’t be working/will be incomplete next week.

Advertisements

About jaoj2

Sha Tin College 10S
This entry was posted in Project 3 - Rockschool Excel Project, Projects, What I Did Today. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s