Monthly Archives: May 2019

Functions

Introducing INDEX function

In my earlier blog ” Flipping from horizontal to vertical, journal entry” I was using INDEX function. However, I thought maybe the INDEX should have been introduced more profoundly first.

A sales report is downloaded from ERP to Excel.

The data should be coherent for pivot report. Date, product and sales volume need to be separated to own columns.

INDEX formula was made for this purpose, also for many other purposes, but at least for this case.

Range A3:D21 is defined as array. After array, row number and column numbers are entered. Row and columns are like coordinates with piece of information is taken from the array.

For example, in our array, array row 2 column 2 equals to 76. Our array row 4 col 1 equals to 2.1.2019.

This is how tacked the case. Report data is in report sheet and pivot data in pivot sheet.

The solution is built with two parts: columns A to C contain the data and columns E to J the column and row values for data.

The date is defined in E and F columns.

As the date is same for three consecutive rows. Therefore E-columns has a new value for every three rows. Row F has a fixed value 1 as the date is in the first column throughout in report data.

The product information is on the top of the report. The row values in G column is static one, column values in column H is varying from 2 to 4.

The sales volume begins in the second row. As sales data is side by side, I column changes value after having had three similar values. The column coordinate value in J column goes 2,3,4,2,3,4.

Here are the cell contents as I have written them.

The date is in A column. In the array the first date is in the second row and first column. B column is for product information. In the report the product is in the first row. The sales volume in C column is extracted starting from second row, columns two to four.

If you want to know how to change all the formula text visible instead of formula values, take file-options and then in advanced page display options for this worksheet, activate “Show formulas in cells instead of their calculated results”.

This blog was to clarify how INDEX function functions.

Excel

SUM in brackets

The sum of cells should be presented in brackets. Sum of 1, 2 and 8 equals to (11).

I have found two ways to tackle this issue.

First one is simply to treat the sum as a key string. The first digit is the first bracket, then the SUM function and finally the second bracket.

The formula in C3 is also here for copy pasting.

=”(” & (SUM(B4:B6)) & “)”

Another option is to change the cell format. Write the normal SUM function to C4 saying = SUM(B4:B6) as usually. Then select home – number of activate cell C3 and press control+1.

Select Custom in write (0) in Type: field.

For further calculations with sums in brackets:

The key string with brackets is considered to be negative.

In the second option, with format cells, Excel understands the C3 as a positive value.

VBA

Saving Excel workbook with a macro

Several reports are downloaded to Excel one by one. Each report will be saved separately as a file to harddisk.

When the report is completed you can just take file-save as without any macros.

It is possible to make a process faster to have a macro saving the file.


Presuming, you take a report by region and period one by one, the data is pasted to columns A to D, row 1 is a header.

It is useful to find some logic for file naming, that you don’t have to name the files separately manually. In the case above, I have taken region_period_number as naming convention. As the name is populated with links, it makes saving the files bit faster.

The macro looks like this:

Sub Z_SaveDocument()

‘ SaveDoc Macro

Dim FileName

FileName = Range(“g11”).Value

    ActiveWorkbook.SaveAs FileName:=”C:\Excel test\” & FileName & “.xlsm”, _

        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

End Sub

The cell G11 defines the file name. I have created a variable to look the macro nicer. The files will be saved in folder C:\Excel test\. You might want to change the file path fitting your own file structure. The extension for files is .xlsm as the files contain a macro. As said, the file name is a variable. As a static value, the file path would be C:\Excel test\ South_7_1.xlsm.

It is useful to create a button in Excel workbook, then executing the macro is pretty fast. I have not saved this macro to personal workbook as the macro is not needed in every Excel workbooks but only this specific Excel workbook.

Enter the data to range A2:D19 and press the button. Looks like nothing happened but check your C:Excel test folder with file explorer.

Paste the new data to the range and press the button again.