File renaming applications are great if you need to rename a lot of files. They can be even better if you can use Excel’s built-in functions such as TRIM, SUBSTITUTE, FIND, LEFT, and RIGHT in order to clean up and generate your new file names. Excel VBA File Renamer for Windows is my take on the idea and can be downloaded here: dailyexcel.net Excel VBA File Renamer for Windows dailyexcel.net Excel VBA File Renamer for Windows enables you to …
Tag: INDIRECT function
How to use the INDIRECT function to define function arrays
In Excel, we can use the INDIRECT function to define arrays that are arguments for other functions, provided that those arrays are referencing the current workbook. Consider the following example: We want to retrieve values from the sheets adjacent to Sheet1, but still in the same workbook. Instead of writing a specific formula for every sheet, we will write a single formula that will link the data from all of the worksheets. We can retrieve our data using …
Combining INDIRECT with ADDRESS
We’ve previously covered how we can use any function to generate a text that could be a valid cell reference, both the column and row parts of the address, and then use the INDIRECT function to convert that text to a cell reference. This process can be greatly enhanced by the ADDRESS function. The ADDRESS function returns a text string that represents the address of a particular cell. Row, column, type of reference (locked or absolute), reference style (A1 …
INDIRECT function
The INDIRECT function returns the reference specified by a text string. The INDIRECT function syntax is as follows: = INDIRECT ( reference_text ; reference_style ) Reference text can be any text that the function can “convert” to a cell reference. Reference style represents the style of referencing cells in Excel. If omitted or TRUE, the reference text is interpreted as an A1 style reference, i.e., the default style of referencing cells in Excel. If FALSE, the reference text is interpreted …