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 column and row part 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 or

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, reference text is interpreted as an A1 style reference i.e. the default style of referencing cells in Excel. If FALSE, reference text is interpreted as an

Lookup with unique identifiers

INDEX function The INDEX function returns the value from a range of cells based on row and column specified in the function arguments. We are simplifying here, the INDEX function can actually return values from one or more arrays – i.e. broader term for lists of data which includes cell ranges. But the most common use is with cell ranges, as that is the typical way of structuring data in Excel.   Syntax of the INDEX function is as follows:

Numbers in Excel

How are numbers stored, formatted and calculated? When dealing with numbers in Excel we should be aware of a few things: numbers in Excel can be formatted in many ways: plain numbers, currency, percentage, date etc. no matter how they are formatted and possibly edited, underlying values saved in cells are still decimal numbers those decimal numbers that Excel uses and calculates with go up to 15 decimal places (and even with that, there are caveats) this is not changed

Text in Excel

Text-related features Excel may have a reputation for crunching numbers, but text formatted data such as names, addresses and descriptions is often encountered. Excel is also often used for processing data from wide variety of unrelated databases, applications and web sites. With those, issues such as numbers formatted as text, text formatted as numbers, multiple text fields combined into a single cell and broken tables are not uncommon. Further complicating things, Excel functions designed to manipulate text formatted data are

Spreadsheet basics

Starting with spreadsheets Spreadsheets are used to store and manipulate data. Spreadsheet data will usually be organized as a table, given that spreadsheet itself is one giant array of empty table cells. Table cells can contain a value (text or number) or a formula. Formulas calculate new values, usually by referencing existing values, and parameters of those calculations can be easily changed by user. These simple concepts can then be used to perform either basic tasks, i.e. you can use