PDA

View Full Version : Formula and Insert the Column problem



Ann_BBO
04-28-2009, 11:43 PM
Hi All,
If my cell (A2) have this formula =INDEX($H2:$IV2,MATCH($H2-30,$H2:$IV2,-1))

Once i insert the column, then whole formula will shift like this: =INDEX($I2:$IV2,MATCH($I2-30,$I2:$IV2,-1))


I know we can use the Indirect function such that the A2 formula will not change even through insert the column but i don't know how to apply in my formula.

Thank,
Ann

Bob Phillips
04-29-2009, 12:05 AM
=INDEX(INDIRECT("$H2:$IV2"),MATCH(INDIRECT("$H2")-30,INDIRECT("$H2:$IV2"),-1))