#REF error in formula

[DanBal]

hello all
I am using the following formula to pull information from one sheet and place it on another.
=IF(ISNA(HLOOKUP(B1,FieldHours!$A2:FieldHours!IV200,2,FALSE)),"NOT LISTED",(HLOOKUP(B1,FieldHours!$A2:FieldHours!IV200,2,FALSE)))

The document is used to track field time and other info.

The problem that i am having is that every week i get the book and excel has changed the first and second FieldHours!$A2 to a $ref!.

I can not figure out why excel is doing this.

should i be using something other than IV200 for the end of my search.

Is there any way to keep excel from changing my fomulas. If i go back and make it the same as it was it seems to work for that sheet only.

If anyone would like to see the book let me know and i will email it to you.
Dbalish at roncomachine com

[swatsp0p]

Is it possible that either the name of the workbook(FieldHours!) or its location has changed?

Do you open the FieldHours workbook? If not, try opening that first, then open your workbook that contains the formula. You say you get the book "every week". Where is it the rest of the time? Is someone else using it? Are they changing something?

#REF! means the formula is looking at an invalid cell reference. http://office.microsoft.com/en-us/as...039481033.aspx is a link to the help site that talks about this issue.

Your reference to column IV should not be a problem with your formula. However, I don't see a need to reference 200 rows, when you formula is going to ONLY pull from row 2 of your table (row 3 of your worksheet in your example) if it finds a match for B1. I don't know if this could be an issue, I wouldn't think so, but I don't know the layout of your workbook.

Good Luck

Bruce

[DanBal]

I am not sure if they are deleting lines or what.

Please help

[DanBal]

the sheet with the formula that is getting messed up is in the same book as the field time and shop time. each week the secratary goes in and deletes numbers and adds numbers as needed.