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

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

To configure COUNTIFS (or COUNTIF) with a variable range, you can use the OFFSET function. In the example shown, the formula in B11 is:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"<>")
This formula...

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 preliminary setup, you can use this approach to search an entire...

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 formula with a dynamic sheet name you can use the INDIRECT function.
In the example shown, the formula in C6 is:
=INDIRECT(B6&"!A1")
Note: The point of this approach is it lets you to build...

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 abbreviate text that contains capital letters, you can try this array formula based on the TEXTJOIN function, a new function available in Office 365 and Excel 2019. You can use this approach to create initials from...

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 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 remove numeric characters from a text string, you can try this experimental formula based on the TEXTJOIN function. In the example shown, the formula in C5 is:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(...