Automatic sorting using workbook formulas

Automatic Sorting using Excel formulas

Optimizing the conclusions presented by an Excel worksheet data processing algorithm usually needs a solution for information sorting in order for it to be displayed top-down by importance factor to the subject.  Additional formulas needed You need to insert the formulas to calculate automatically the position of each item in the list based on it’s […]

Combobox VBA – populating with items

Combobox VBA

Comboboxes are user friendly data input forms that can make the difference between a quality looking form and a archaic data input screen. Defining combobox in Excel For this step you must go to the Excel Developer menu, make sure you have this menu since it doesn’t come activated in Excel Options. Select the Insert […]

Count words in Excel using worksheet formulas

Count Words in Excel

The solution for this problem is simple. The words are separated by spaces and these spaces need to be counted in order to find out how many words there are inside the text. Worksheet formula to count words in a text If we eliminate the spaces with the Substitute formula and count how many eliminations […]

Reading from SQL table in VBA Excel

Reading from SQL

Reading from SQL tables usually means that there is a need for supplementary data processing and manipulation before conclusions and information being provided to the decision making person. Excel is the best application to satisfy this need and some very useful and ergonomic reports can be developed in order to provide fresh and accurate information. […]

Excel file unsaveable

Some companies use Excel files through intranet networks for all their staff to have access to certain information and automated reports. This usually means that this kind of file should be unsavable by the staff in order to preserve a certain degree of accuracy and format. Setting Excel file as unsavable Without altering the menu […]

Connecting to SQL Server in VBA Excel

Connecting to SQLServer VBA

Building automated reports in Excel by connecting directly to an ERP database is a good shortcut to have fresh and accurate information to base your decisions on. This method facilitates data processing and manipulation in VBA before results and conclusions are displayed and a further availability for organizing, calculations and sorting after Excel tables are […]

Formatting number in a text output to show thousands separator

Formatting with thousands separator

Formatting number inside text output in Excel will save the aspect of the report generated. Sometimes you need to optimize the time taken to update a excel spreadsheet report by using formulas which output text and numbers. If you use only the concatenating operator the result will be that the number inside the text is […]

Split text into sentences in Excel

Split text into sentences

The formulas to split text in Excel are very simple. All you need to use is Left(), Right() and Find(). Spliting text First you have to store the whole text into a cell and from there start writing the formulas in the image below. If the text is too large for a single cell you […]