To increment a reference created as text inside the INDIRECT function, you can use the CELL function. In the example shown, the formula in D5 is:
=INDIRECT($B$5&"!"&CELL("address",A1))...

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 build a formula with a dynamic reference to an Excel Table name, you can use the INDIRECT function with concatenation as needed. In the example shown, the formula in L5 is:
=SUM(INDIRECT(K5&"[Amount]...

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 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 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,...

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&...

To use data validation to allow a list of specific characters only, you can use a rather complicated array formula based on the COUNT, MATCH, and LEN functions. In the example shown, data validation is applied with this...

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 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 count the number of Mondays, Fridays, Sundays, etc. between two dates you can use an array formula that uses several functions: SUMPRODUCT, WEEKDAY, ROW, and INDIRECT.
In the example shown, the formula in cell E6 is...

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 create an array of numbers like {1;2;3;4;5} you can use a formula based on the ROW and INDIRECT functions. This technique is most often used in array formulas that need a numeric array for processing of some kind. In...

To use COUNTIFS with a variable table column, you can use INDEX and MATCH to find and retrieve the column for COUNTIFS. In the example shown, the formula in H5 is:
=COUNTIFS(INDEX(Table1,0,MATCH(G5,Table1[#Headers],0...