I have cells in one worksheet looking up values in the same XLS file,
but in a different worksheet. The function seems to working well,
except for one thing -- if I change the first parameter, lookup_value,
it breaks (displays #N/A).

Here is the form of the function:

=HLOOKUP(E$68,Categories!$A$24:$Z$63,ROW()-23,FALSE)

Let's say the value in E$68 is 2 when I open the file. The value that
appears in the cell is
17'
which is the correct dereferenced value.

Now I change the value in E68 to 3. The dereferenced value now reads
#N/A.

So I change the value in E68 back to 2. The value is still #N/A. I
have to undo twice to get it to go back to the originally correct
value of 17'.

I wanted to look into this more, so I changed the value in the lookup
function from
=HLOOKUP(E$68,Categories!$A$24:$Z$63,ROW()-23,FALSE)

to

=HLOOKUP(2,Categories!$A$24:$Z$63,ROW()-23,FALSE)

The problem still happens.

Getting curious now, I looked at the format of the referenced cell in
the Categories worksheet. That format is Text. I change the format
to Number (0 decimals). The original 17' is still in the referencing
cell. It only changes to #N/A when I change the value in the indexing
cell (E68), as described above.

Now I am really curious, but totally bumfuzzled (not to mention
needing to get some work done). I have run out of ideas, so I have
come to usenet. Can anyone help me figure out what I need to do to
get it to behave correctly?

- Paul Schrum
Earth Tech | AECOM
Raleigh, NC