To get cell content with a given row and column number, you can use the ADDRESS function together with INDIRECT. In the example shown, the formula in G6 is:
=INDIRECT(ADDRESS(G4,G5))
How this formula works
The Excel...

To search multiple worksheets in a workbook for a value and return a count, you can use a formula based on the COUNTIF and INDIRECT functions. With some setup, you use this approach to search an entire workbook for a...

To conditionally sum identical ranges that exist in separate worksheets, all in one formula, you can do so with the SUMIF function + INDIRECT, wrapped in SUMPRODUCT. In the example, the formula looks like this:
=...

To remove non-numeric characters from a text string, you can try this experimental formula based on the TEXTJOIN function, new in Excel 2016. In the example shown, the formula in C5 is:
{=TEXTJOIN("",TRUE,...

If you need to sum or add the bottom values in a range, you can do so with a formula that uses the SMALL function wrapped inside the SUMPRODUCT function. In the generic form of the formula (above), rng represents a...

To test if a worksheet name exists in a workbook, you can use a formula based on the ISREF and INDIRECT functions. In the example shown, the formula in C5 is:
=ISREF(INDIRECT(B5&"!A1"))
How this formula...

To remove numeric characters from a text string, you can try this experimental formula based on the TEXTJOIN function, new in Excel 2016. In the example shown, the formula in C5 is:
=TEXTJOIN("",TRUE,IF(...

To build a dynamic worksheet reference - a reference to another workbook that is created with a formula based on variables that may change - you can use a formula based on the INDIRECT function.
In the example shown,...

To sum the top values in a range, you can use a formula based on the LARGE function, wrapped inside the SUMPRODUCT function. In the generic form of the formula (above), rng represents a range of cells that contain...

To allow a dynamic lookup table, you can use the INDIRECT function with named ranges inside of VLOOKUP.
In the example shown the formula in G5 is:
=VLOOKUP(F5,INDIRECT(E5),2,0)
Background
The purpose of this...

To create a formula with a "locked" reference – a reference that won't be adjusted during copy or paste, or when rows and columns are changed in a worksheet – you can use the INDIRECT function.
How this formula works...

To convert a column letter to an regular number (e.g. 1, 10, 26, etc.) you can use a formula based on the INDIRECT and COLUMN functions.
In the example shown, the formula in C5 is:
=COLUMN(INDIRECT(B5&"1...

To use count a non-contiguous range with criteria, you can use the COUNTIF function together with INDIRECT and SUM. In the example shown, cell I5 contains this formula:
=SUM(COUNTIF(INDIRECT({"B5:B8","...

To calculate work hours between two dates with a custom schedule, you can use a formula based on the WEEKDAY and SUMPRODUCT functions, with help from ROW, INDIRECT, and MID. In the example shown, the formula in F8 is...

To create a lookup with a variable sheet name, you can use the VLOOKUP function together with the INDIRECT function.
In the example shown, the formula in C5 is:
=VLOOKUP($B5,INDIRECT("'"&C$4&...