Indirect formulas

Hi Pawan,

Thank you for your question and welcome to the forum.

The INDIRECT function returns a reference to a range. You can use this function to create a reference that won't change if row or columns are inserted in the worksheet. Or, use it to create a reference from letters and numbers in other cells.

e.g.

Data Sheet A2 =INDIRECT("'" & A2 & "'!" & B2)

The formula above returns the value in the cell that is referenced from the sheet name in A2 cell A2.

You can use Indirect to display the value of the cell and if new row are inserted above the formula still returns the original cell rather than the value in the next cell down.

e.g.

Cell A4 = 56 Cell A10 = Indirect("A4") this will display 56

If we then insert a new row above row 4 then the formula will still refer to the the value of cell A4 instead of moving te vlaue down to cell A5.

I hope this answers your question.

Regards

Simon