Odd Problem with HLOOKUP -- Any change breaks it

[Paul Schrum]

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

 

[Franz Verga]

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 post:67b8aa25-a6fa-443f-9189-d9de1ea19a1d@s24g2000vbp.googlegroups.com,

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

 

[Paul Schrum]

Franz,

Thanks for your advice.

Please note that in the first step, the problem had nothing to do with
changing formats. I simply changed the value in the reference cell
(E68). At open the cell contains "2" to make it lookup another column
with heading of "2". When I enter the same value, 2, in the reference
cell, the value changes from 17' (a valid value) to #N/A.

All the other discussion about changing formats was discussing things
I tried to do to get around the original problem. But they are not
the actual original problem.

Now, perhaps I have misunderstood your post. If I have, please try to
clarify for me.

Thanks.

- Paul

 

[Franz Verga]

Paul the problem *is* about formatting.

When you input manually a number in a cell, Excel automatically switch the
format of the cell from text to number, so this is why you have the #N/A
result.

If you format accordingly your reference row and your input cell (E68) both
with the number format *and* apply the procedure I described before to the
reference row, you should solve your problem.

But maybe I misunderstood your problem...


Nel post:bf3485a9-91ed-41db-922e-d0123bdbaa35@e10g2000vbe.googlegroups.com,

Franz,

Thanks for your advice.

Please note that in the first step, the problem had nothing to do with
changing formats. I simply changed the value in the reference cell
(E68). At open the cell contains "2" to make it lookup another column
with heading of "2". When I enter the same value, 2, in the reference
cell, the value changes from 17' (a valid value) to #N/A.

All the other discussion about changing formats was discussing things
I tried to do to get around the original problem. But they are not
the actual original problem.

Now, perhaps I have misunderstood your post. If I have, please try to
clarify for me.

Thanks.

- Paul

--
(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

 

[Paul Schrum]

Franz,

I did what you suggest (I think). That is, I changed the format of
the indexing row (row 68, containing the indexing number) from text to
number. When I changed the format, I could then change the values in
*68 and the dereferenced values showed correctly -- no #N/A. In other
words, your suggestion works.

Thanks a lot for you help.

- Paul

 

[Paul Schrum]

To anyone who may be reading, and to Franz,

After working in the file some more, I have come to a different theory
on the source of my original problem. I have indexed the file as a
table in Access. When a query is open in Access that includes data
from the Excel file, the excel file is locked for saving. My new
theory is that this file locking being done by Access seems also to be
causing the HLOOKUP problem.

When I reported to Franz earlier that doing what he suggested worked,
I suppose that it may have worked because coincidentally the Access
file was closed at that moment and was therefore not locking the XL
file.

- Paul