In this project, I helped to connect the first sheet, used as UI, to other sheets used as tracking sheets for invoices.
After all fields had been filled, clicking on "Submit" would send the data to its corresponding sheet.
The tracking sheet contains fields for each field on the invoice, as well as four new fields: invoice value, expiration date, DTE, and Bonus. The first field is calculated by multiplying the amount of cubic meters by its unit price; this value is then multiplied by another number to calculate the bonus amount. The Expiration Date field simply takes the invoice's date and adds sixty days. Finally, the DTE stands for days to expiration. That is just taking the expiration date and subtracting today's date. This gives you the number of days until the invoice expires.
When a new invoice is submitted, it is also sent to a master sheet so that we can see an overall view of each entry.
In this project, I optimized and streamlined Excel files used for client reporting, reducing the time required for each report from one hour to twenty minutes.
Here we compare two columns, B and C, and then we look at the change in column D. For cells B9 to B15 and B29 to B41, we have to manually input the numbers, whereas optimization comes in from cells B16 to B28. What these cells do is look for specific words in the keywords column and check how many of each rank there are based on different conditionals. In column D, we compare B and C by doing a subtraction—if the number is bigger than zero (0), then it turns red; otherwise it remains black.
If the number in column B is greater than the number in column C, it will turn red. This indicates a positive change in keyword rank. Then these red numbers are used to calculate cell B28—using Visual Basic—we count all cells in column B that are red.
I used Google Sheets to scrape various potential clients' websites.
The formula: =IMPORTXML, helped me obtain
basic information from their websites, such as: