Help cell refernece not to delete when delete columns on another sheet

[pkam2010]

HI

can someone help. i have a formalu below

=IFERROR(INDEX('sheet2'!$A$5:$S$32,MATCH('sheet1'!B19,'sheet2'!$A$5:$A$17,0),5),"")

however when i need to add/dleete some columns in sheet2 and but i want it to stay and look at the above, but when i add/delete the columns the cells it is referencing to changes to #REF !

how do i stop this from happening

 

[jim may]

With the formula in Sheet1 -- Try:

=IFERROR(INDEX(INDIRECT("sheet2!$A$5:$S$32"),MATCH(B19,INDIRECT("sheet2!$A$5:$A$17"),0),5),"")

 

[pkam2010]

Hi

it is not returning anything, the cell is blank.

when i delete/add rows its great it still loking at the cells in sheet2 but it is not bring up that value it is suppose to find the cell is just blank.

 

[jim may]

HI

can someone help. i have a formalu below

=IFERROR(INDEX('sheet2'!$A$5:$S$32,MATCH('sheet1'!B19,'sheet2'!$A$5:$A$17,0),5),"")

however when i need to add/dleete some columns in sheet2 and but i want it to stay and look at the above, but when i add/delete the columns the cells it is referencing to changes to #REF !

how do i stop this from happening

OK, let's try this again.. I assume (at some point) the above Formula worked OK. Right?
So what exactly are you doing in your sheet2 - be specific - tell me exactly column and row what
are you doing to cause the formula "to break", or produce the #REF !? Post back...

Jim