This long period of time I haven't posted anything because we've been making some Excel practices (and I've also been to New York!!!). At first we remembered how to make some simple functions as multiplying, dividing, adding, subtracting and other more difficult functions as the average, inserting graphics, the "DGET", "DMAX", "DMIN", "DCOUNT" "DCOUNTA" or "IF" functions, the "IF" function within other "IF" functions and so. We also practiced format functions (percentages, money symbols, colour of the field depending on something...).
The first important project we've made was an electronic invoice with a record of products, clients and the charged amounts depending on what they buy.
The first thing I had to have in this excel book was a front or home page where you include the company data and some buttons to link to the other pages. You can do that with a macro or adding a hyperlink by right clicking on the button you've drawn or inserted, choosing "Hyperlink", "place from this folder" and the sheet you want to make that button access.
Then it was time to fill in the data within the clients' sheet. You have to fill in the name, surname, adress, phone number and e-mail adress manually but then you can add a special format to set their personal codes with three digits, for instance, as I did in my "B" column. I also set a "DGET" function in my "C" column so that it shows the three first letters of their surname (=EXTRAE(D4;1;3)). Then I added a conditional format on my "F" column to green colour if the client is a man (M) or yellow color if it's a woman (F) by setting that options in a new rule on "Condicional Format".
The easiest sheet is the products' catalog one. You just have to fill in the products' code, its name and its price. I also added the price with taxes. The code to do this consists just in multiplying the price by the percentage of the tax and add it to the price. For the code you can do the same as in the clients' sheet, making it have three digits automatically by adding zeros. As in the rest of the sheets I added buttons to access the other sheets.
The last sheet is the bill. At first you have to know the client who's buying so with filling the client code, his or her data has to be showed. This is possible with the function =BUSCARV(C5;Clientes!B4:L17;6;FALSO) in the case of my bill, just changing the number of column, the third factor of the function. The first one is the box in which the code is writen and the second one the chart in which the data must be searched. With the products it's the same but with another chart and another code box (=BUSCARV(C10;Catálogo!B4:F22;2;FALSO)). Then the tax and the discount are added.
To erase the data you just have to make a macro as the image shows, and erase all the fields you want (the code boxes and the number of products) and stop the recording of the macro. Then you add the macro to a button. To print it's just the same but pressing the print button while recording the macro.
I hope it was useful and if you have any question, just let me know. See you soon.
The first thing I had to have in this excel book was a front or home page where you include the company data and some buttons to link to the other pages. You can do that with a macro or adding a hyperlink by right clicking on the button you've drawn or inserted, choosing "Hyperlink", "place from this folder" and the sheet you want to make that button access.
Then it was time to fill in the data within the clients' sheet. You have to fill in the name, surname, adress, phone number and e-mail adress manually but then you can add a special format to set their personal codes with three digits, for instance, as I did in my "B" column. I also set a "DGET" function in my "C" column so that it shows the three first letters of their surname (=EXTRAE(D4;1;3)). Then I added a conditional format on my "F" column to green colour if the client is a man (M) or yellow color if it's a woman (F) by setting that options in a new rule on "Condicional Format".To erase the data you just have to make a macro as the image shows, and erase all the fields you want (the code boxes and the number of products) and stop the recording of the macro. Then you add the macro to a button. To print it's just the same but pressing the print button while recording the macro.
I hope it was useful and if you have any question, just let me know. See you soon.
