have been using Vlookup to reference a 2nd sheet to verify cashed checks
based on the check number on the first sheet. using
=VLOOKUP(B59856,CLEAR,2,FALSE)
which has been working fine. the spreadsheet breaks 40k lines and is excell
2000 so just to be sure it wasn't a memory problem or anything like that I
split it for a new spread and it's under 8k lines for the formula. for some
reason the formula comes up with n/a's for results even though there are
exact matched on the other sheet...ideas?

Assuming defined range clear is correctly defined then most common cause of
what you describe is data mismatch. If B59856 is a numeric value, not text,
then the lookup range should be too.Test with

=ISNUMBER(B59856) and

=ISNUMBER(X288)

where X288 is the cell that should match B59856, do you get TRUE for both?

"Tossaire" wrote:

have been using Vlookup to reference a 2nd sheet to verify cashed checks
based on the check number on the first sheet. using
=VLOOKUP(B59856,CLEAR,2,FALSE)
which has been working fine. the spreadsheet breaks 40k lines and is excell
2000 so just to be sure it wasn't a memory problem or anything like that I
split it for a new spread and it's under 8k lines for the formula. for some
reason the formula comes up with n/a's for results even though there are
exact matched on the other sheet...ideas?

How would B59856 *ever* be a number ... if that's what you see in the
formula bar?
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"daddylonglegs" wrote in message
...
Assuming defined range clear is correctly defined then most common cause of
what you describe is data mismatch. If B59856 is a numeric value, not text,
then the lookup range should be too.Test with

=ISNUMBER(B59856) and

=ISNUMBER(X288)

where X288 is the cell that should match B59856, do you get TRUE for both?

"Tossaire" wrote:

have been using Vlookup to reference a 2nd sheet to verify cashed checks
based on the check number on the first sheet. using
=VLOOKUP(B59856,CLEAR,2,FALSE)
which has been working fine. the spreadsheet breaks 40k lines and is
excell
2000 so just to be sure it wasn't a memory problem or anything like that I
split it for a new spread and it's under 8k lines for the formula. for
some
reason the formula comes up with n/a's for results even though there are
exact matched on the other sheet...ideas?

They both came back true on the first sheet, after specifying that the second
cell was on the 2nd sheet.

"daddylonglegs" wrote:

Assuming defined range clear is correctly defined then most common cause of
what you describe is data mismatch. If B59856 is a numeric value, not text,
then the lookup range should be too.Test with

=ISNUMBER(B59856) and

=ISNUMBER(X288)

where X288 is the cell that should match B59856, do you get TRUE for both?

"Tossaire" wrote:

have been using Vlookup to reference a 2nd sheet to verify cashed checks
based on the check number on the first sheet. using
=VLOOKUP(B59856,CLEAR,2,FALSE)
which has been working fine. the spreadsheet breaks 40k lines and is excell
2000 so just to be sure it wasn't a memory problem or anything like that I
split it for a new spread and it's under 8k lines for the formula. for some
reason the formula comes up with n/a's for results even though there are
exact matched on the other sheet...ideas?

the contents of that cell.

"RagDyeR" wrote:

How would B59856 *ever* be a number ... if that's what you see in the
formula bar?
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"daddylonglegs" wrote in message
...
Assuming defined range clear is correctly defined then most common cause of
what you describe is data mismatch. If B59856 is a numeric value, not text,
then the lookup range should be too.Test with

=ISNUMBER(B59856) and

=ISNUMBER(X288)

where X288 is the cell that should match B59856, do you get TRUE for both?

RagDyeR,

daddylonglegs is refering to Cell B59856 (or the value in this cell) and not
the actual text/string of "B59856".

So with the formula he/she posted, they are testing whether the value in
cell B59856 is a number or not.

HTH,

Conan
"RagDyeR" wrote in message
.. .
How would B59856 *ever* be a number ... if that's what you see in the
formula bar?
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"daddylonglegs" wrote in message
...
Assuming defined range clear is correctly defined then most common cause
of
what you describe is data mismatch. If B59856 is a numeric value, not
text,
then the lookup range should be too.Test with

=ISNUMBER(B59856) and

=ISNUMBER(X288)

where X288 is the cell that should match B59856, do you get TRUE for both?

"Tossaire" wrote:

have been using Vlookup to reference a 2nd sheet to verify cashed checks
based on the check number on the first sheet. using
=VLOOKUP(B59856,CLEAR,2,FALSE)
which has been working fine. the spreadsheet breaks 40k lines and is
excell
2000 so just to be sure it wasn't a memory problem or anything like that
I
split it for a new spread and it's under 8k lines for the formula. for
some
reason the formula comes up with n/a's for results even though there are
exact matched on the other sheet...ideas?

What is your formula now and if you use any defined names, what range do they refer to?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Tossaire" wrote in message ...
| have been using Vlookup to reference a 2nd sheet to verify cashed checks
| based on the check number on the first sheet. using
| =VLOOKUP(B59856,CLEAR,2,FALSE)
| which has been working fine. the spreadsheet breaks 40k lines and is excell
| 2000 so just to be sure it wasn't a memory problem or anything like that I
| split it for a new spread and it's under 8k lines for the formula. for some
| reason the formula comes up with n/a's for results even though there are
| exact matched on the other sheet...ideas?

If all data is numeric your formula should work, I'm assuming all your
numbers are integers?

How is the range CLEAR defined?

"Tossaire" wrote:

They both came back true on the first sheet, after specifying that the second
cell was on the 2nd sheet.

"daddylonglegs" wrote:

Assuming defined range clear is correctly defined then most common cause of
what you describe is data mismatch. If B59856 is a numeric value, not text,
then the lookup range should be too.Test with

=ISNUMBER(B59856) and

=ISNUMBER(X288)

where X288 is the cell that should match B59856, do you get TRUE for both?

"Tossaire" wrote:

have been using Vlookup to reference a 2nd sheet to verify cashed checks
based on the check number on the first sheet. using
=VLOOKUP(B59856,CLEAR,2,FALSE)
which has been working fine. the spreadsheet breaks 40k lines and is excell
2000 so just to be sure it wasn't a memory problem or anything like that I
split it for a new spread and it's under 8k lines for the formula. for some
reason the formula comes up with n/a's for results even though there are
exact matched on the other sheet...ideas?

Exactly, Conan, thank you

btw - I'm a he - otherwise I'd be mummylonglegs !!

"Conan Kelly" wrote:

RagDyeR,

daddylonglegs is refering to Cell B59856 (or the value in this cell) and not
the actual text/string of "B59856".

So with the formula he/she posted, they are testing whether the value in
cell B59856 is a number or not.

HTH,

Conan
"RagDyeR" wrote in message
.. .
How would B59856 *ever* be a number ... if that's what you see in the
formula bar?
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"daddylonglegs" wrote in message
...
Assuming defined range clear is correctly defined then most common cause
of
what you describe is data mismatch. If B59856 is a numeric value, not
text,
then the lookup range should be too.Test with

=ISNUMBER(B59856) and

=ISNUMBER(X288)

where X288 is the cell that should match B59856, do you get TRUE for both?

"Tossaire" wrote:

have been using Vlookup to reference a 2nd sheet to verify cashed checks
based on the check number on the first sheet. using
=VLOOKUP(B59856,CLEAR,2,FALSE)
which has been working fine. the spreadsheet breaks 40k lines and is
excell
2000 so just to be sure it wasn't a memory problem or anything like that
I
split it for a new spread and it's under 8k lines for the formula. for
some
reason the formula comes up with n/a's for results even though there are
exact matched on the other sheet...ideas?

Formula is =vlookup(B59845,CLEAR,2,FALSE) only thing that I have specific is
to go to the next array for the lookup...

"Niek Otten" wrote:

What is your formula now and if you use any defined names, what range do they refer to?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Tossaire" wrote in message ...
| have been using Vlookup to reference a 2nd sheet to verify cashed checks
| based on the check number on the first sheet. using
| =VLOOKUP(B59856,CLEAR,2,FALSE)
| which has been working fine. the spreadsheet breaks 40k lines and is excell
| 2000 so just to be sure it wasn't a memory problem or anything like that I
| split it for a new spread and it's under 8k lines for the formula. for some
| reason the formula comes up with n/a's for results even though there are
| exact matched on the other sheet...ideas?

What does "go to the next array for the lookup" mean; what do you actually do if you do that?
How is CLEAR defined?
You write about "results". So is there more than one formula? What are they? Which one gives what result?

Try being a bit more precise, so we can help :-)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



"Tossaire" wrote in message ...
| Formula is =vlookup(B59845,CLEAR,2,FALSE) only thing that I have specific is
| to go to the next array for the lookup...
|
| "Niek Otten" wrote:
|
| What is your formula now and if you use any defined names, what range do they refer to?
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "Tossaire" wrote in message ...
| | have been using Vlookup to reference a 2nd sheet to verify cashed checks
| | based on the check number on the first sheet. using
| | =VLOOKUP(B59856,CLEAR,2,FALSE)
| | which has been working fine. the spreadsheet breaks 40k lines and is excell
| | 2000 so just to be sure it wasn't a memory problem or anything like that I
| | split it for a new spread and it's under 8k lines for the formula. for some
| | reason the formula comes up with n/a's for results even though there are
| | exact matched on the other sheet...ideas?
|
|
|

Ok,
the formula is on the first sheet. it references CLEAR which is the
second. =VLOOKUP(B59856,CLEAR,2,FALSE) this formula worked for 40k lines
and now is coming up with #na. checked through and couldn't see any data
entry problems and the information is correct on the CLEAR sheet. i apologize
if my answers are a little vague, i'm not one an excel pro like you guys
trying to help me :)