|As noted at the site given below, the Excel Help file doesn't do a very good job of showing the power of the INDIRECT function.|
In the simplest of terms, the INDIRECT function tells Excel to use the argument as a reference, regardless if that argument is the contents of a cell, the result of another formula, a text string, etc. Depending on how you build the argument, it can reference a single cell, a range, a sheet, etc.
e.g. Put this in Row 1:
ROW() will evaluate to 1 which we then concatenate (&) with the text string "!$F:$H" to create a text string of:
INDIRECT converts this string into a reference so Excel actually sees the VLOOKUP formula as:
A simliar example, but using the contents of cells instead of a function would be something like:
Whatever is in A1 would be picked up as a Sheet name and whatever is in D1 would be pickup as a Row value.
Let's say A1 contains the word Profits, either as the result of a function or as simple text.
Let's say D1 contained 44, either as the result of a function or as a simple number.
The INDIRECT function would return the same thing as =Profits!B44 would.
Obviously the difference is that =Profits!B44 is "static" but with INDIRECT, as you change the contents of A1 and/or D1, the result will change.
See here for more: