INDIRECT function adds realy dynamism to your formulas.

Suppose you want to sum the values in Col A and currently have 10 items (in cells A2 to A11). You can use the following formula to get the result;
=SUM(A2:A11)

As you add more items you need to update the end cell address from A11.

INDIRECT gives you a way to avoid this.

Let us say you have the last cell address in B2...

Update your formula to;
=SUM(INDIRECT("A2:" & B2))

You can have just the row number of the last cell in B2 (eg. 100) then you can use
=SUM(INDIRECT("A2:A" & B2))

This is same as
=SUM(A2:A100)

INDIRECT basically evaluates its argument (("A2:A" & B2) and passes on the result (A2:A100) to SUM.

All you need to take care is that the result from INDIRECT is what the function surroungding your INDIRECT function expects.