Odd Problem with HLOOKUP -- Any change breaks it
 

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

Odd Problem with HLOOKUP -- Any change breaks it
 

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

Odd Problem with HLOOKUP -- Any change breaks it
 

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

On Dec 22, 6:28*pm, "Franz Verga" wrote:
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

Odd Problem with HLOOKUP -- Any change breaks it
 

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

On Dec 22, 6:28 pm, "Franz Verga" wrote:
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



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

Odd Problem with HLOOKUP -- Any change breaks it
 

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

On Dec 22, 7:15*pm, "Franz Verga" wrote:
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...

Odd Problem with HLOOKUP -- Any change breaks it
 

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