If you think you cannot learn anything new every day, think again.
This actually applied to my most recent exercise conducted for #teammalaysia when I was asked to help out to track on our recent t-shirt ordering and buying "expedition".
You can actually read the summarised progress here on how it how far it has progressed.
Over here, I would like to share what I have learned in my most recent attempt to make full use of Google Sheets (which can be viewed in public for the benefit of transparency).
@aikido.hung would have been proud of me, but then again, to him it is just easy peasy; because he is the master of Spreadsheets!
I would believe that most of the time we are very much used to the basic use of Google Sheets (or Excel Spreadsheets), and most of the time (like me, maybe not you) would probably just manually fill in everything that needs to be filled in; and this usually could cause human error compilation.
Especially when your data is very important for the supplier to read through later.
(oh no no no, the supplier will not see this; but will see the summarised version based on what we have collected. The sheet is still in progress of becoming better)
Anyways
Here are some of the things that I have learned recently that perhaps, just perhaps could be of great use for you in the future!
Tip 1: The If statements
The IF statement is actually very useful especially when you are looking into different types of responses.
I however did use a very manual style to do this (until I perfect it in the future), but if you noticed that different "X" mark in the cell produces different cash value.
A very good example is the application if the order requires express postage poslaju .
A t-shirt that is 2XL and below all comprises of MYR 23 in total for full customised printing of a t-shirt with at least 6 colours on it; but if a buyer request for a postage service, if you do not use a formula to do so, you will end up having doing all the calculations manually, where you can make mistakes.
Therefore, in every cell in column N (Total Price), this rule was applied:
I figured a screen capture will work better as there are colour codes to it
This applies to those who used cash sales, and if there is and indication of "Y" in cell L (That includes poslaju services) it will add an extra MYR 9 into the service charge (including packaging)
Now that, is actually quite simple and most intermediate users would have known this formula.
I can assure you, I have plenty of admin staffs who still know nuts about this, and they use garbage in garbage out method to manually key in. The most they would use is SUM and addition and subtraction.
Now come the INTERESTING PART
Tip 2: Colour coding your cells based on certain rules IN A RANGE.
Yes, because most people would have done the cell-by-cell custom colouring; but to save time, there is this really cool feature in Google Sheets (I have not checked Excel sheet) that will help you to colour code your cells based on the criteria you already set.
1. Where to find it?
When you right click your cell, you would already see that there is a "Conditional Formatting)" feature stated at your Google Sheets. All you have to do is to click on it and it will bring you to a special window at the side for you to set it up.
2. Specify your range
Usually when you click a single cell, it would just show you just one cell; but if you would like to specify an entire column to look at, you will need to type this (and it will automatically formulate for you when you press "Done" later)
Leave the to range number unfilled so that the system will intelligently fill up the default range for you; otherwise you will need to specify yourself.
3. Tell Sheets what you want
This is where the magic happens. At the value of formula you would need to write in your specific thing to look into; BUT, so far, as I have not used the IF statement yet, you should specify a simple value only instead of complicating things because one range of cell can actually receive multiple rules to look into (sequentially)
Notice that I only specify one cell as a reference to look into ?
There were countless times I tried to set it as a range to spot for the word "Sabah"; but often times it gave me an error.
After vigorous searching thanks to Presearch that gives more unfiltered search results compared to using Google search directly; I found out that somehow because of the range I have set to look on, each of the row in column U (for example) will automatically counter check each row in column R.
Notice that I put a $ before R? That is because I only want the rule to check on nothing but column R; otherwise the whole formula will go crazy because it will check the entire range including the application range U.
After that, you can just apply the style you want exactly as you wished so that it will show just like what you want.
4. Repeat step 2 and step 3 if you want more rules to apply to the same range
As I have mentioned before, you actually CAN make multiple rules for the system to check sequentially on each rule. These rules become "If statements" or "Case select" statements (for those programming junkies) so you won't have to be frustrated to make if formulas into the format cell if... section.
I have not tried adding multiple match conditions into the formula just yet; but if you already know about it, do share your comments or even point me to your post if you already have written one.
Knowledge is power and sharing is wealth. Sometimes we keep the things we learn so much to ourselves we ended up overloading our lives with unnecessary burden when people come and try to ask you for help.
These are already quite common and humans should not race and compete with each other, but instead offer ourselves in terms of service to those who truly need help and perhaps, from that point, our validated characteristics will win the hearts of more than what we can charge them for, and that, actually generates more wealth than you can think of.Anyways, enough with the philosophical part above. I hope from this post you have learned a neat trick or two to impress not only your bosses but make your lives easier, as much as this discovery helped me so much!
