Explanation
At the core, this formula extracts the number, adds the increment, and joins the number to the original text in the right format.
Working from the inside out, this formula first extracts the numeric portion of the string in column B using the RIGHT function:
RIGHT(B5,3) // returns "001"
The result returned is actually text like “001”, “003”, etc. but when we add the numeric value from C, Excel automatically changes the next to a number and performs the addition:
RIGHT(B5,3)+C5 // returns 2
Next, this numeric result goes into the TEXT function as the value, with a number format of “000”. This pads the number with zeros as needed:
TEXT(2,"000") // returns "002"
Finally, this text string is joined to the text “Item " using concatenation:
="Item "&TEXT(2,"000")
Which returns a final result of “Item 002”.
Explanation
Consider a simple dynamic reference to Sheet2 using the INDIRECT in a formula like this:
=INDIRECT($B$5&"!"&"A1")
If we change the sheet name in B5 to another (valid) name, INDIRECT will return a reference to A1 in the new sheet.
However, if we copy this formula down the column, the reference to A1 won’t change, because “A1” is hardcoded as text.
To solve this problem, we use the CELL function to generate a text reference from a regular cell reference:
CELL("address",A1)
With “address” as the first argument, and A1 as the second argument, the CELL function returns a string like “$A$1”. Because A1 is a regular cell reference, it will increment normally as the formula is copied down the column. The result in D5:D9 is a series of formulas like this:
=INDIRECT("Sheet2!$A$1")
=INDIRECT("Sheet2!$A$2")
=INDIRECT("Sheet2!$A$3")
=INDIRECT("Sheet2!$A$4")
=INDIRECT("Sheet2!$A$5")
In each case, INDIRECT resolves each text string to a reference and Excel returns the value at the given cell in Sheet2.
Note: both INDIRECT and CELL are volatile functions and recalculate with every worksheet change. This can cause performance problems in more complex worksheets.
