(C) 2009 - 2014 by Mourad Louha · All rights reserved

All posts tagged Formula

In early December 2012, I have released the new websites for the Excel formula translator. The site in a similar design to Excel Ticker is available in German, English, French, Italian and Hungarian and is also part of my planned network of websites focusing on Microsoft Excel.

Excel Formula Translator

The websites provide, in addition to the integration of the online tool, a reference to the translations of all Excel functions from Microsoft Excel. In case where the translation on an Excel function differs from one Excel version to another, these translations are listed separately. And all translations are linked to the Microsoft Online Help in the corresponding language. A few days ago, I did an update and released the websites in Italian and Hungarian.

Many thanks to Roberto Mensa and Krisztina Szabó for supporting and helping me to translate the sites. Roberto is an Excel e VBA enthusiast expert. He is mainly active on Italian online forums and he regularly publishes his work on his sites E90E50 and E90E50fx. Krisztina is the manager of Excel Hero LinkedIn Group and member of the Frankens Team.

If you would like to support us to translate the website into more languages, we welcome your comments here or write us an email to the address mentioned on our imprint page.

 

I am currently studying some Windows Registry accesses from Excel & Co. A formula in an Excel sheet helps me to split these entries into its components; meaning the path elements and the value. The location where a file is stored can be addressed in a similar manner like a registry key. So, I had the idea to write this article and use my formula for splitting a file location in its path and file name.

Excel 2010

The storage location of a file usually starts with a drive letter specification followed by a series of folder names separated by separator and ends with the filename. For a Windows operating system, the separator is a backslash.

Depending on where the file is located, the path is sometimes shorter, sometimes longer. To retrieve the file name with an Excel formula, we have to know the position of the last separator in the path. Then, we will be able to use the Excel function “Right()” and extract the file name.

Read more

Someone asked in my favorite German forum about Microsoft Office if it is possible to dynamically build a reference to an Excel range located in another file. The following solution addresses a range in an external file by using the Excel functions OFFSET(), INDIRECT() and ADDRESS().

Let’s suppose, we have some files which all have an identical structure for the data. The screenshot below shows 4 files, each one providing fictive data for the days of one week. The values are stored in each file in the cells C4 to C10 of a sheet named “Data”.

Excel 2010

Read more