#REF error in formula

D

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
 
S

swatsp0p

Is it possible that either the name of the workbook(FieldHours!) or it
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 th
book "every week". Where is it the rest of the time? Is someone els
using it? Are they changing something?

#REF! means the formula is looking at an invalid cell reference.
http://office.microsoft.com/en-us/assistance/HP052039481033.aspx is
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 i
going to ONLY pull from row 2 of your table (row 3 of your worksheet i
your example) if it finds a match for B1. I don't know if this could b
an issue, I wouldn't think so, but I don't know the layout of you
workbook.

Good Luck

Bruc
 
D

DanBal

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