#REF error in formula
 

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!IV20 0,2,FALSE)),"NOT
LISTED",(HLOOKUP(B1,FieldHours!$A2:FieldHours!IV20 0,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


--
DanBal
------------------------------------------------------------------------
DanBal's Profile: http://www.excelforum.com/member.php...o&userid=24079
View this thread: http://www.excelforum.com/showthread...hreadid=395403

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


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=395403

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

Please help


--
DanBal
------------------------------------------------------------------------
DanBal's Profile: http://www.excelforum.com/member.php...o&userid=24079
View this thread: http://www.excelforum.com/showthread...hreadid=395403

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.


--
DanBal
------------------------------------------------------------------------
DanBal's Profile: http://www.excelforum.com/member.php...o&userid=24079
View this thread: http://www.excelforum.com/showthread...hreadid=395403