INDIRECT worksheet function

Tuesday, February 2nd, 2016 by admin admin

I have a formula which is used to find out if a particular description is available. It works well except that I have to keep readjusting the range in the formula because it is shortened every time I delete the rows within the range. In the end, I have to make use of the INDIRECT worksheet function to freeze it so that it is not affected by the deleting of rows.

Excel Table

Thursday, January 14th, 2016 by admin admin

Excel Table is a new function in Excel. It can organize information neater compared to a normal Excel worksheets. But one thing Excel Table cannot do is insert cut cells from another row of the table. For example, I would like to swop the values between E5 and E6. The way I would do it is to cut the cell in E6, go to E5 and press Ctrl and + key together. The values will exchange positions. But in Excel Table, this cannot be done.

Paul McKenna: I chose my wife by Excel spreadsheet

Monday, January 4th, 2016 by admin admin

This is not one of the Excel uses I would recommend but Paul definitely used it correctly to organize information and see things clearer.

http://www.telegraph.co.uk/news/celebritynews/12078792/Paul-McKenna-chose-wife-Kate-Davey-by-Excel-spreadsheet.html

VBA Excel – Goto Function

Wednesday, December 30th, 2015 by admin admin

In one of the projects that I was working on, I wrote a set of Macros VBA code to select and highlight the cells that I want to keep. It behaves the same way as when the GOTO function is applied manually. The following code is what I used to highlighted the filtered cells yellow:

            Set rCcells = Selection.SpecialCells(xlCellTypeVisible)
            With rCcells
                .Interior.Color = RGB(255, 255, 0)
            End With

It was working perfectly well until I encounter a case when the filtered result returns zero row. The macros highlighted all the hidden rows instead. After searching high and low for the code to capture this scenario, I finally found that the way to do it is to use the COUNTIF function to count the number of records that the filtered results is likely to generate. This exceptional scenario that I encountered can be captured easily if the COUNTIF function returns the value zero which is zero count. To make it work perfectly in your codes, you need to run the COUNTIF first. If there is more than one count, then go ahead with the GOTO function. The codes to do that is given below. In the code below, I assume that I am trying count how many cells in K2:K100 contain the number 100. If there is at least one, continue with the GOTO Function. If not, skip it.

        xx = Application.WorksheetFunction.CountIf(ActiveSheet.Range("K2:K100"), "100")
        'Highlight cells yellow if auto-filter returns at least one row
        If xx > 0 Then
            ActiveSheet.Range("K2:K" & dlast_row).Select
            Set rCcells = Selection.SpecialCells(xlCellTypeVisible)
            With rCcells
                .Interior.Color = RGB(255, 255, 0)
            End With
        End If

 

A Sum formula that always adds new rows

Thursday, July 9th, 2015 by admin admin

What do you do if you have a list and you constantly have to add the new records to the grand total using the SUM formula? If your SUM formula is immediately below the record, you will have to keep changing your range as the SUM formula would not include the new row added before the grand total row.

One work around is to add in an empty row and include that in your SUM formula. Whenever there is a need to add new records, add it before this blank row so that the SUM formula will always include the new records in the SUM formula.

What if you are not allowed to keep a blank row for this purpose? Today, I am going to show you a new solution.

Assuming that the SUM formula is in A11 and is currently adding the numbers in the range A1:A10. Instead of creating a SUM formula that simply adds up the range A1:A10 [=SUM(A1:A10)], create the range in text format and use the ROW formula. The range including the inverted commas becomes “A1:A”&Row()-1. The ROW()-1 will always calculate one row before the SUM formula. Since this is a text and cannot be read as a range, you need to the INDIRECT formula to convert the text based range into a proper range. Adding the INDIRECT and SUM formula, you new SUM formula should be =SUM(INDIRECT(“A1:A”&ROW()-1)). This new SUM formula will save you the time to change the range constantly and you will not have to live with an empty row between your data and the grand total anymore.

Microsoft Excel can be installed as a standalone Android App

Friday, June 26th, 2015 by admin admin

Previously, you have to install Microsoft Office App (Excel, PowerPoint and Words) to use Excel. Now they come as separate Apps and you can pick the App you wish to install on your Andriod Phone. Watch the YouTube video for more details.

 

Do not mix numbers with formulas

Wednesday, June 17th, 2015 by admin admin

In Excel, it is better to separate numbers and formulas into different cells. For example, if you wish to multiple by a row of numbers by a certain rate, it is better to create one row of numbers and another row for the rate. Then add in a 3rd row to multiple the first 2 rows together. In this way, you can always see your numbers and rate you entered and you can also change the rates easily. Do not try to save rows by putting the number and rate into one cell or row.

Microsoft launched a new keyboard for Android Tablets

Monday, March 2nd, 2015 by admin admin

Microsoft launched a new keyboard for Android tablet. The new keyboard basically contains a keypad on the right. It is going to be helpful for those who are have to enter lots of numbers quickly on their tablets. So far, there is no mention of an iPhone or iPad version.  Microsoft Excel Android Keyboard

Conditional Formatting Stop if True

Friday, October 10th, 2014 by admin admin

In conditional formatting, we can set more than one rule to format the cells. If the rules do not conflict with each other, then all is well. But what happens when more than one rule turn TRUE? According to Microsoft website, the earlier rule that turns takes precedence over the latter rules. So if the earlier rule format the cells Red and there comes a second rule that formats the cells Blue, the cell will remain as Red.

Then in this case, what is the purpose of the option Stop IF True?

That option works if you have a different format for the 2 rules. If the earlier rule format the cell Red and the latter rule format the Font colour to Blue, both rules will be applied. This is different from the earlier example when the rules are using the same formatting, change the cells colour. In this example, the 2 rules apply different kind of format (one is cell colour and the other is font colour) and therefore, both are applied. If you do not wish to apply the latter rule, change in font colour when the earlier rule turn TRUE, then check the option STOP IF TRUE on the earlier rule. This will make the conditional formatting stop checking the latter rule and therefore, format font colour will not be applied when the earlier rule (format cell colour) turns TRUE.

For a more detailed explanation, please refer to this write-up on Microsoft website http://office.microsoft.com/en-sg/excel-help/manage-conditional-formatting-rule-precedence-HA010342674.aspx

 

Splitting Fixed Assets into multiple rows

Tuesday, September 23rd, 2014 by admin admin

A past participant was working on a Fixed Asset Register and one of the tasks she is required to do is to split up the Fixed Assets into multiple rows, based on the quantity. Given below is an example of what has to be done.

a) A00016 Well Management Pte Ltd 10297 AM00000000002685 4.00 APOT-0150

19-Dec-01

369.48 369.48 0.00
b) A00016 Well Management   Pte Ltd 10297 AM00000000002685 1.00 APOT-0150

19-Dec-01

92.37 92.37 0.00
c) A00016 Well Management   Pte Ltd 10297 AM00000000002685 1.00 APOT-0150

19-Dec-01

92.37 92.37 0.00
d) A00016 Well Management   Pte Ltd 10297 AM00000000002685 1.00 APOT-0150

19-Dec-01

92.37 92.37 0.00
e)A00016 Well Management   Pte Ltd 10297 AM00000000002685 1.00 APOT-0150

19-Dec-01

92.37 92.37 0.00

Meaning I need to split item (a) of qty 4 equally into (b) to (e) of qty 1 and cost p/qty in each line.

This would have been quite straight forward if the quantity for all the Fixed Assets are the same. The problem is, they are not. Therefore, we have to use VLOOKUP with approximate match to determine the number of rows required for each line of Fixed Assets and populate them into the relevant rows. The worksheet function COlUMN is used to help us identify the column index number for the VLOOKUP formula. And in this example, I also did a quick demonstration on filling up a range to a specific number almost instantly. Watch the video below to learn the application of each function.

If you have benefitted from the video, we would love to hear from you. Let us know which part of the solution is useful to you and how it has benefitted you.

 

 

s2Member®