Hi Paul,

the trick is that when you change the format of already filled cells from
number to text or viceversa, it will work only for the next input, while
what is already inside the cell will remeber the original format.

So, you have to change really the format of the referenced row: first you
have to change the format, of the row from text to number, but is not
sufficient for the data that are already in the range, so you have to input
1 in an empty cell, copy it and than select the range of the referenced row
and make past special and select Values, and Multiply. At this point you
can cancel the input 1.

Now, it should work.




Nel ,
Paul Schrum ha scritto:
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



--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy