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 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 remove non-numeric characters from a text string, you can try this experimental formula based on the TEXTJOIN function, a new function available in Office 365 and Excel 2019. In the example shown, the formula in C5...

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

You can reverse a text string with the TEXTJOIN and MID functions, by using an array constant. In the example shown, the formula in C5 is:
=TEXTJOIN("",1,MID(B5,{10,9,8,7,6,5,4,3,2,1},1))
How this formula...

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 count weekdays (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 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 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...

To reference a named range on another sheet, you can use the INDIRECT function with the required sheet syntax. In the example shown, the formula in D6 is:
=SUM(INDIRECT("'"&B6&"'!"&C6...

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