Hi All,

I have implemented the "INDIRECT" function on A2 and A3. A2 find out the "one month ago" date on a "$H2:$IV2" data. Then, A3 get the corrsponding data base on A2. Note that I will insert the latest data in column H and H2 will be the latest date.

A2=INDEX(INDIRECT("$H2:$IV2"),MATCH(INDIRECT("$H2")-30,INDIRECT("$H2:$IV2"),-1))

A3=HLOOKUP(INDIRECT("A$2"),INDIRECT("$H$2:$IV$79"),2,FALSE)

It is OK when i insert the new column to column H and not affect the A2,A3. However, i will insert the new ROW(s) on the top row. Then, the "INDIRECT" function will not work since "INDIRECT" function fixed on the cell.

For example. if i insert the new row on top row, A2 becomes A3 and A3 becomes A4. Then i would like the formula will change to:

A3=INDEX(INDIRECT("$H3:$IV3"),MATCH(INDIRECT("$H3")-30,INDIRECT("$H3:$IV3"),-1))

A4=HLOOKUP(INDIRECT("A$3"),INDIRECT("$H$3:$IV$80"),2,FALSE)

How to implement the "INDIRECT" function work on inserting column but not inserting row? Thanks

Ann