I have inherited two spreadsheets that are linked and a VLOOKUP in one is
referring to a range in the other. The VLOOKUPs all work on the existing
data but as when I add new data, bearing in mind the formatting of the cells
and the order of the lookup range, those do not work. I get the #N/A error.

Any ideas?

What formula?
What format and why does that matter?
What values in the table and in the search argument?


--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"charlene leblanc" <charlene wrote in
message ...
I have inherited two spreadsheets that are linked and a VLOOKUP in one is
referring to a range in the other. The VLOOKUPs all work on the existing
data but as when I add new data, bearing in mind the formatting of the
cells
and the order of the lookup range, those do not work. I get the #N/A
error.

Any ideas?

Have you extended the lookup range to include the new data?

Often this is caused because you are looking for "123" and the source range
is 123. The string "123" does not match the number 123 (regardless of which
is the term being looked for and the which is in the source data).

If a cell is formatted as Text and you enter 123, you actually get "123"
If a cell contains the number 123 and you then format it as text, the numer
123 is still stored in the cell.

Perhaps something like this is the problem.

--
Regards,
Tom Ogilvy


"charlene leblanc" <charlene wrote in
message ...
I have inherited two spreadsheets that are linked and a VLOOKUP in one is
referring to a range in the other. The VLOOKUPs all work on the existing
data but as when I add new data, bearing in mind the formatting of the
cells
and the order of the lookup range, those do not work. I get the #N/A
error.

Any ideas?

When all else has failed for me, Charlene, I have done this.

Insert a new column next to your Vlookup root data column

enter this formula: =(A2&"")+0 (assuming your root data starts in cell A2)

slide a copy paste of the formula all the way down to copy your whole data
column

replace your root data with the special paste:values of the new column

Charlene, I don't have to tell you to back up your file before attempting
anything anyone recommends to you.

Don't ask me why this might work, but it has resolved my vlookup issues in
the past.

Peace



"charlene leblanc" wrote:

I have inherited two spreadsheets that are linked and a VLOOKUP in one is
referring to a range in the other. The VLOOKUPs all work on the existing
data but as when I add new data, bearing in mind the formatting of the cells
and the order of the lookup range, those do not work. I get the #N/A error.

Any ideas?

If works if you are looking up numbers because the result is a number. See
my previous post in this thread.

--
Regards,
Tom Ogilvy

"Cyberindio" wrote in message
...
When all else has failed for me, Charlene, I have done this.

Insert a new column next to your Vlookup root data column

enter this formula: =(A2&"")+0 (assuming your root data starts in cell A2)

slide a copy paste of the formula all the way down to copy your whole data
column

replace your root data with the special paste:values of the new column

Charlene, I don't have to tell you to back up your file before attempting
anything anyone recommends to you.

Don't ask me why this might work, but it has resolved my vlookup issues in
the past.

Peace



"charlene leblanc" wrote:

I have inherited two spreadsheets that are linked and a VLOOKUP in one
is
referring to a range in the other. The VLOOKUPs all work on the
existing
data but as when I add new data, bearing in mind the formatting of the
cells
and the order of the lookup range, those do not work. I get the #N/A
error.

Any ideas?

Sorry for being so vague. I have workbook ONE with a VLOOKUP in column O.
The VLOOKUP uses column A and looks for that value in column T in workbook
TWO and retrieves the value from column Z in the corresponding row.

Workbook ONE
Column A Column O
12345 VLOOKUP()
23456 VLOOKUP()

Workbook TWO
Column T Column Z
12345 XXXXX
23456 YYYYY

When making a change to an existing row in workbook TWO, (i.e. changing the
XXXXX in column Z to XXXYZ, the change is reflected in column O of workbook
ONE. That works as it should.

When I add a new row to workbook TWO and a corresponding on in workbook ONE,
copying the VLOOKUP formula from the existing row that works as above, I get
the #N/A error. The data in column T is sorted as it needs to be for the
VLOOKUP to succeed. To follow from the illustration above adding 12789 in
both column A of ONE and column T of TWO should give the VLOOKUP for that row
a result of XXABC. Instead it gives the #N/A error. All columns are
formatted as General, but I've also tried formatting them as Text and it made
no difference.

Workbook ONE
Column A Column O
12345 VLOOKUP()
23456 VLOOKUP()
12789 VLOOKUP()

Workbook TWO
Column T Column Z
12345 XXXXX
12789 XXABC
23456 YYYYY

The VLookup formula is as follows:
=VLOOKUP(A15,'\\BBIC\CML\[TWO.xls]One Fund'!$T:$Z,7,FALSE)

Hopefully this illustration has answered your questions and you may have
some idea what is going on.

Charlene

"Niek Otten" wrote:

What formula?
What format and why does that matter?
What values in the table and in the search argument?


--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"charlene leblanc" <charlene wrote in
message ...
I have inherited two spreadsheets that are linked and a VLOOKUP in one is
referring to a range in the other. The VLOOKUPs all work on the existing
data but as when I add new data, bearing in mind the formatting of the
cells
and the order of the lookup range, those do not work. I get the #N/A
error.

Any ideas?

It's worth a try, but I'm not sure what you mean by 'root data'. Using the
documented syntax of

"VLOOKUP(lookup_value,table_array,col_index_num,ra nge_lookup)"

Do you mean doing a copy of the lookup_value column or the table_array, or
the range_lookup? I guess I could do all three ....

Thanks,
Charlene

"Cyberindio" wrote:

When all else has failed for me, Charlene, I have done this.

Insert a new column next to your Vlookup root data column

enter this formula: =(A2&"")+0 (assuming your root data starts in cell A2)

slide a copy paste of the formula all the way down to copy your whole data
column

replace your root data with the special paste:values of the new column

Charlene, I don't have to tell you to back up your file before attempting
anything anyone recommends to you.

Don't ask me why this might work, but it has resolved my vlookup issues in
the past.

Peace



"charlene leblanc" wrote:

I have inherited two spreadsheets that are linked and a VLOOKUP in one is
referring to a range in the other. The VLOOKUPs all work on the existing
data but as when I add new data, bearing in mind the formatting of the cells
and the order of the lookup range, those do not work. I get the #N/A error.

Any ideas?

Format of the cells is identical and the new row is within the range. See my
entry in response to Niek Otten for an illustration.

Thanks,
Charlene

"Tom Ogilvy" wrote:

Have you extended the lookup range to include the new data?

Often this is caused because you are looking for "123" and the source range
is 123. The string "123" does not match the number 123 (regardless of which
is the term being looked for and the which is in the source data).

If a cell is formatted as Text and you enter 123, you actually get "123"
If a cell contains the number 123 and you then format it as text, the numer
123 is still stored in the cell.

Perhaps something like this is the problem.

--
Regards,
Tom Ogilvy


"charlene leblanc" <charlene wrote in
message ...
I have inherited two spreadsheets that are linked and a VLOOKUP in one is
referring to a range in the other. The VLOOKUPs all work on the existing
data but as when I add new data, bearing in mind the formatting of the
cells
and the order of the lookup range, those do not work. I get the #N/A
error.

Any ideas?

OK I did the formula to replace the contents of both the lookup_value and the
table_array columns with no change in the result. The range_lookup is a text
value.

Charlene

"charlene leblanc" wrote:

It's worth a try, but I'm not sure what you mean by 'root data'. Using the
documented syntax of

"VLOOKUP(lookup_value,table_array,col_index_num,ra nge_lookup)"

Do you mean doing a copy of the lookup_value column or the table_array, or
the range_lookup? I guess I could do all three ....

Thanks,
Charlene

"Cyberindio" wrote:

When all else has failed for me, Charlene, I have done this.

Insert a new column next to your Vlookup root data column

enter this formula: =(A2&"")+0 (assuming your root data starts in cell A2)

slide a copy paste of the formula all the way down to copy your whole data
column

replace your root data with the special paste:values of the new column

Charlene, I don't have to tell you to back up your file before attempting
anything anyone recommends to you.

Don't ask me why this might work, but it has resolved my vlookup issues in
the past.

Peace



"charlene leblanc" wrote:

I have inherited two spreadsheets that are linked and a VLOOKUP in one is
referring to a range in the other. The VLOOKUPs all work on the existing
data but as when I add new data, bearing in mind the formatting of the cells
and the order of the lookup range, those do not work. I get the #N/A error.

Any ideas?

You say everything is fine and is as it should be for vlookup to work, but
vlookup doesn't work.

Simple logic will tell you that the first part of the statement is then
incorrect.

--
Regards,
Tom Ogilvy

"charlene leblanc" wrote in
message ...
OK I did the formula to replace the contents of both the lookup_value and
the
table_array columns with no change in the result. The range_lookup is a
text
value.

Charlene

"charlene leblanc" wrote:

It's worth a try, but I'm not sure what you mean by 'root data'. Using
the
documented syntax of

"VLOOKUP(lookup_value,table_array,col_index_num,ra nge_lookup)"

Do you mean doing a copy of the lookup_value column or the table_array,
or
the range_lookup? I guess I could do all three ....

Thanks,
Charlene

"Cyberindio" wrote:

When all else has failed for me, Charlene, I have done this.

Insert a new column next to your Vlookup root data column

enter this formula: =(A2&"")+0 (assuming your root data starts in cell
A2)

slide a copy paste of the formula all the way down to copy your whole
data
column

replace your root data with the special paste:values of the new column

Charlene, I don't have to tell you to back up your file before
attempting
anything anyone recommends to you.

Don't ask me why this might work, but it has resolved my vlookup
issues in
the past.

Peace



"charlene leblanc" wrote:

I have inherited two spreadsheets that are linked and a VLOOKUP in
one is
referring to a range in the other. The VLOOKUPs all work on the
existing
data but as when I add new data, bearing in mind the formatting of
the cells
and the order of the lookup range, those do not work. I get the
#N/A error.

Any ideas?

I just tried to tell you that the format of the cells is not the determining
factor. The determining factor is how the value is stored in the cell. You
can check how with =IsText() and =IsNumber

--
Regards,
Tom Ogilvy

Tom,

I think you may have been onto something with the istext and isnumber
functions. However, I have now discovered what I believe to be the cause.

The two spreadsheets were opened in different sessions of Excel and the
reference was to the other workbook including the drive assignment.

When I open the second spreadsheet as a second workbook in the same Excel
session, and do exactly the same changes, I have no problem and no error. I
discovered that when I tried to reference the cell of the second workbook for
the istext function.

Not sure whether the underlying cause was a memory issue or what, but in any
case, my problem is resoved as long as I open both workbooks in the same
Excel session.

Closing off this request for assistance.
Thank you very much.
Charlene LeBlanc

"Tom Ogilvy" wrote:

I just tried to tell you that the format of the cells is not the determining
factor. The determining factor is how the value is stored in the cell. You
can check how with =IsText() and =IsNumber

--
Regards,
Tom Ogilvy

You definitely don't want multiple sessions. Glad you solved your problem.

--
Regards,
Tom Ogilvy

"charlene leblanc" wrote in
message ...
Tom,

I think you may have been onto something with the istext and isnumber
functions. However, I have now discovered what I believe to be the cause.

The two spreadsheets were opened in different sessions of Excel and the
reference was to the other workbook including the drive assignment.

When I open the second spreadsheet as a second workbook in the same Excel
session, and do exactly the same changes, I have no problem and no error.
I
discovered that when I tried to reference the cell of the second workbook
for
the istext function.

Not sure whether the underlying cause was a memory issue or what, but in
any
case, my problem is resoved as long as I open both workbooks in the same
Excel session.

Closing off this request for assistance.
Thank you very much.
Charlene LeBlanc

"Tom Ogilvy" wrote:

I just tried to tell you that the format of the cells is not the
determining
factor. The determining factor is how the value is stored in the cell.
You
can check how with =IsText() and =IsNumber

--
Regards,
Tom Ogilvy

Tom
I believe this is the problem I have just been struggling with. Can you
tell me please how I can convert a large chunk of data already entered and
stored as numbers into text?
Thanks
Ann

"Tom Ogilvy" wrote:

Have you extended the lookup range to include the new data?

Often this is caused because you are looking for "123" and the source range
is 123. The string "123" does not match the number 123 (regardless of which
is the term being looked for and the which is in the source data).

If a cell is formatted as Text and you enter 123, you actually get "123"
If a cell contains the number 123 and you then format it as text, the numer
123 is still stored in the cell.

Perhaps something like this is the problem.

--
Regards,
Tom Ogilvy


"charlene leblanc" <charlene wrote in
message ...
I have inherited two spreadsheets that are linked and a VLOOKUP in one is
referring to a range in the other. The VLOOKUPs all work on the existing
data but as when I add new data, bearing in mind the formatting of the
cells
and the order of the lookup range, those do not work. I get the #N/A
error.

Any ideas?

Select the cells you want to convert and then run the macro.

Sub converttotext
for each cell in selection
v = cell.Text
cell.NumberFormat = "@"
cell.Value = "'" & cell.Text
Next
End Sub

--
Regards,
Tom Ogilvy

"Ann Mc" <Ann wrote in message
...
Tom
I believe this is the problem I have just been struggling with. Can you
tell me please how I can convert a large chunk of data already entered and
stored as numbers into text?
Thanks
Ann

"Tom Ogilvy" wrote:

Have you extended the lookup range to include the new data?

Often this is caused because you are looking for "123" and the source
range
is 123. The string "123" does not match the number 123 (regardless of
which
is the term being looked for and the which is in the source data).

If a cell is formatted as Text and you enter 123, you actually get "123"
If a cell contains the number 123 and you then format it as text, the
numer
123 is still stored in the cell.

Perhaps something like this is the problem.

--
Regards,
Tom Ogilvy


"charlene leblanc" <charlene wrote in
message ...
I have inherited two spreadsheets that are linked and a VLOOKUP in one
is
referring to a range in the other. The VLOOKUPs all work on the
existing
data but as when I add new data, bearing in mind the formatting of the
cells
and the order of the lookup range, those do not work. I get the #N/A
error.

Any ideas?

How do I run the macro below? I have never run a macro. Thank you so much!
- JMcFarland

"Tom Ogilvy" wrote:

Select the cells you want to convert and then run the macro.

Sub converttotext
for each cell in selection
v = cell.Text
cell.NumberFormat = "@"
cell.Value = "'" & cell.Text
Next
End Sub

--
Regards,
Tom Ogilvy

"Ann Mc" <Ann wrote in message
...
Tom
I believe this is the problem I have just been struggling with. Can you
tell me please how I can convert a large chunk of data already entered and
stored as numbers into text?
Thanks
Ann

"Tom Ogilvy" wrote:

Have you extended the lookup range to include the new data?

Often this is caused because you are looking for "123" and the source
range
is 123. The string "123" does not match the number 123 (regardless of
which
is the term being looked for and the which is in the source data).

If a cell is formatted as Text and you enter 123, you actually get "123"
If a cell contains the number 123 and you then format it as text, the
numer
123 is still stored in the cell.

Perhaps something like this is the problem.

--
Regards,
Tom Ogilvy


"charlene leblanc" <charlene wrote in
message ...
I have inherited two spreadsheets that are linked and a VLOOKUP in one
is
referring to a range in the other. The VLOOKUPs all work on the
existing
data but as when I add new data, bearing in mind the formatting of the
cells
and the order of the lookup range, those do not work. I get the #N/A
error.

Any ideas?

Start he
David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

JMcFarland wrote:

How do I run the macro below? I have never run a macro. Thank you so much!
- JMcFarland

"Tom Ogilvy" wrote:

Select the cells you want to convert and then run the macro.

Sub converttotext
for each cell in selection
v = cell.Text
cell.NumberFormat = "@"
cell.Value = "'" & cell.Text
Next
End Sub

--
Regards,
Tom Ogilvy

"Ann Mc" <Ann wrote in message
...
Tom
I believe this is the problem I have just been struggling with. Can you
tell me please how I can convert a large chunk of data already entered and
stored as numbers into text?
Thanks
Ann

"Tom Ogilvy" wrote:

Have you extended the lookup range to include the new data?

Often this is caused because you are looking for "123" and the source
range
is 123. The string "123" does not match the number 123 (regardless of
which
is the term being looked for and the which is in the source data).

If a cell is formatted as Text and you enter 123, you actually get "123"
If a cell contains the number 123 and you then format it as text, the
numer
123 is still stored in the cell.

Perhaps something like this is the problem.

--
Regards,
Tom Ogilvy


"charlene leblanc" <charlene wrote in
message ...
I have inherited two spreadsheets that are linked and a VLOOKUP in one
is
referring to a range in the other. The VLOOKUPs all work on the
existing
data but as when I add new data, bearing in mind the formatting of the
cells
and the order of the lookup range, those do not work. I get the #N/A
error.

Any ideas?







--

Dave Peterson

After I by right-click on the sheettab, choose 'view code', and then paste in
the macro, how do I run the macro and how do I name the macro?

Also, when I paste the macro below, Excel automatically puts parenthesis ()
after the first statement

Sub converttotext()

I tried putting the column (B) that I need the data converted but that
didn't work, so then I tried (B9:B65536) and that didn't work either. Any
suggestions?

"Dave Peterson" wrote:

Start he
David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

JMcFarland wrote:

How do I run the macro below? I have never run a macro. Thank you so much!
- JMcFarland

"Tom Ogilvy" wrote:

Select the cells you want to convert and then run the macro.

Sub converttotext
for each cell in selection
v = cell.Text
cell.NumberFormat = "@"
cell.Value = "'" & cell.Text
Next
End Sub

--
Regards,
Tom Ogilvy

"Ann Mc" <Ann wrote in message
...
Tom
I believe this is the problem I have just been struggling with. Can you
tell me please how I can convert a large chunk of data already entered and
stored as numbers into text?
Thanks
Ann

"Tom Ogilvy" wrote:

Have you extended the lookup range to include the new data?

Often this is caused because you are looking for "123" and the source
range
is 123. The string "123" does not match the number 123 (regardless of
which
is the term being looked for and the which is in the source data).

If a cell is formatted as Text and you enter 123, you actually get "123"
If a cell contains the number 123 and you then format it as text, the
numer
123 is still stored in the cell.

Perhaps something like this is the problem.

--
Regards,
Tom Ogilvy


"charlene leblanc" <charlene wrote in
message ...
I have inherited two spreadsheets that are linked and a VLOOKUP in one
is
referring to a range in the other. The VLOOKUPs all work on the
existing
data but as when I add new data, bearing in mind the formatting of the
cells
and the order of the lookup range, those do not work. I get the #N/A
error.

Any ideas?







--

Dave Peterson

Don't rightclick on the sheettab, view code, and paste.

If you did this, then remove that code.

Open the VBE (alt-f11)
Hit ctrl-f4 (to see the project explorer)
Select your project
Insert|Module
And paste into that code window.

Then back to excel
Select your range to fix
Tools|macro|macros|Select converttotext
and click Run

(alt-f8 will bring up that tools|macro|macros dialog, too.)

JMcFarland wrote:

After I by right-click on the sheettab, choose 'view code', and then paste in
the macro, how do I run the macro and how do I name the macro?

Also, when I paste the macro below, Excel automatically puts parenthesis ()
after the first statement

Sub converttotext()

I tried putting the column (B) that I need the data converted but that
didn't work, so then I tried (B9:B65536) and that didn't work either. Any
suggestions?

"Dave Peterson" wrote:

Start he
David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

JMcFarland wrote:

How do I run the macro below? I have never run a macro. Thank you so much!
- JMcFarland

"Tom Ogilvy" wrote:

Select the cells you want to convert and then run the macro.

Sub converttotext
for each cell in selection
v = cell.Text
cell.NumberFormat = "@"
cell.Value = "'" & cell.Text
Next
End Sub

--
Regards,
Tom Ogilvy

"Ann Mc" <Ann wrote in message
...
Tom
I believe this is the problem I have just been struggling with. Can you
tell me please how I can convert a large chunk of data already entered and
stored as numbers into text?
Thanks
Ann

"Tom Ogilvy" wrote:

Have you extended the lookup range to include the new data?

Often this is caused because you are looking for "123" and the source
range
is 123. The string "123" does not match the number 123 (regardless of
which
is the term being looked for and the which is in the source data).

If a cell is formatted as Text and you enter 123, you actually get "123"
If a cell contains the number 123 and you then format it as text, the
numer
123 is still stored in the cell.

Perhaps something like this is the problem.

--
Regards,
Tom Ogilvy


"charlene leblanc" <charlene wrote in
message ...
I have inherited two spreadsheets that are linked and a VLOOKUP in one
is
referring to a range in the other. The VLOOKUPs all work on the
existing
data but as when I add new data, bearing in mind the formatting of the
cells
and the order of the lookup range, those do not work. I get the #N/A
error.

Any ideas?







--

Dave Peterson


--

Dave Peterson

This worked. Thanks SO much.

"Dave Peterson" wrote:

Don't rightclick on the sheettab, view code, and paste.

If you did this, then remove that code.

Open the VBE (alt-f11)
Hit ctrl-f4 (to see the project explorer)
Select your project
Insert|Module
And paste into that code window.

Then back to excel
Select your range to fix
Tools|macro|macros|Select converttotext
and click Run

(alt-f8 will bring up that tools|macro|macros dialog, too.)

JMcFarland wrote:

After I by right-click on the sheettab, choose 'view code', and then paste in
the macro, how do I run the macro and how do I name the macro?

Also, when I paste the macro below, Excel automatically puts parenthesis ()
after the first statement

Sub converttotext()

I tried putting the column (B) that I need the data converted but that
didn't work, so then I tried (B9:B65536) and that didn't work either. Any
suggestions?

"Dave Peterson" wrote:

Start he
David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

JMcFarland wrote:

How do I run the macro below? I have never run a macro. Thank you so much!
- JMcFarland

"Tom Ogilvy" wrote:

Select the cells you want to convert and then run the macro.

Sub converttotext
for each cell in selection
v = cell.Text
cell.NumberFormat = "@"
cell.Value = "'" & cell.Text
Next
End Sub

--
Regards,
Tom Ogilvy

"Ann Mc" <Ann wrote in message
...
Tom
I believe this is the problem I have just been struggling with. Can you
tell me please how I can convert a large chunk of data already entered and
stored as numbers into text?
Thanks
Ann

"Tom Ogilvy" wrote:

Have you extended the lookup range to include the new data?

Often this is caused because you are looking for "123" and the source
range
is 123. The string "123" does not match the number 123 (regardless of
which
is the term being looked for and the which is in the source data).

If a cell is formatted as Text and you enter 123, you actually get "123"
If a cell contains the number 123 and you then format it as text, the
numer
123 is still stored in the cell.

Perhaps something like this is the problem.

--
Regards,
Tom Ogilvy


"charlene leblanc" <charlene wrote in
message ...
I have inherited two spreadsheets that are linked and a VLOOKUP in one
is
referring to a range in the other. The VLOOKUPs all work on the
existing
data but as when I add new data, bearing in mind the formatting of the
cells
and the order of the lookup range, those do not work. I get the #N/A
error.

Any ideas?







--

Dave Peterson


--

Dave Peterson