#Ref Error

[howard]

I have a vlookup combined with an index Function.


If I open up the workbook from where the formula looks up the values, I get a #Ref error in the formula in the destination workbook

=IFERROR(IF(VLOOKUP($B370&"",'[BR1March.xls]#Ref '!$A$1:$D$65536,4,FALSE)<=0,-VLOOKUP($B370&"",'[BR1March.xls]#Ref '!$A$1:$D$65536,4,FALSE),VLOOKUP($B370&"",'[BR1March.xls]#Ref '!$A$1:$D$65536,4,FALSE)),0)

Is there any way to avoid this ?

 

[Special-K99]

1. Your formula can be simplified to make it much shorter by simply placing ABS() around a single VLOOKUP

2. #REF looks like it had a cell reference in it before the error occurred referring to a sheet name.

Change your formula to this

=IFERROR(ABS(VLOOKUP($B370&"",INDIRECT("'[BR1March.xls]"&xxx&"'!$A$1:$D$65536"),4,FALSE)),0)

Replace xxx with whatever the cell reference was.

 

[steve the fish]

Looks to me like you deleted a sheet

 

[howard]

Thanks for your input