Prev: How to run a macro automatically when the file is opened?
Next: Allow Users to Edit Ranges
From: Pas on 23 Apr 2010 19:29
Does anybody know why I am getting #NA with this formula. It was working OK
awhile ago????

=INDEX($B$2:$BJ$402,MATCH(B411,$A$2:$A$402,0),MATCH(B410,$B$1:$BJ$1,0))
From: Dave Peterson on 23 Apr 2010 20:03
Put these portions of your formula in two other cells:

=MATCH(B411,$A$2:$A$402,0)
=MATCH(B410,$B$1:$BJ$1,0)

One or both will return that #N/A error. You'll have to find out why there's a
mismatch (spelling error, too many spaces, ...).


Pas wrote:
>
> Does anybody know why I am getting #NA with this formula. It was working OK
> awhile ago????
>
> =INDEX($B$2:$BJ$402,MATCH(B411,$A$2:$A$402,0),MATCH(B410,$B$1:$BJ$1,0))

--

Dave Peterson
From: Pas on 23 Apr 2010 20:46
Cheers Dave, I'll try that and let you know how I get on.

"Dave Peterson" wrote:

> Put these portions of your formula in two other cells:
>
> =MATCH(B411,$A$2:$A$402,0)
> =MATCH(B410,$B$1:$BJ$1,0)
>
> One or both will return that #N/A error. You'll have to find out why there's a
> mismatch (spelling error, too many spaces, ...).
>
>
> Pas wrote:
> >
> > Does anybody know why I am getting #NA with this formula. It was working OK
> > awhile ago????
> >
> > =INDEX($B$2:$BJ$402,MATCH(B411,$A$2:$A$402,0),MATCH(B410,$B$1:$BJ$1,0))
>
> --
>
> Dave Peterson
> .
>
From: Pas on 24 Apr 2010 15:58
Yes the second formula results in #NA. I have a textbox linked to cell "B410"
, which has a formula. When I clear the formula on that cell it works fine.
For some weird reason it's giving #NA because the fact that the cell has a
formula???
Any ideas?

"Pas" wrote:

> Cheers Dave, I'll try that and let you know how I get on.
>
> "Dave Peterson" wrote:
>
> > Put these portions of your formula in two other cells:
> >
> > =MATCH(B411,$A$2:$A$402,0)
> > =MATCH(B410,$B$1:$BJ$1,0)
> >
> > One or both will return that #N/A error. You'll have to find out why there's a
> > mismatch (spelling error, too many spaces, ...).
> >
> >
> > Pas wrote:
> > >
> > > Does anybody know why I am getting #NA with this formula. It was working OK
> > > awhile ago????
> > >
> > > =INDEX($B$2:$BJ$402,MATCH(B411,$A$2:$A$402,0),MATCH(B410,$B$1:$BJ$1,0))
> >
> > --
> >
> > Dave Peterson
> > .
> >
From: Dave Peterson on 24 Apr 2010 16:45
Nope.

It's giving you the error because the value in that textbox doesn't match any of
the cells in B1:BJ1.

==
Are the values you're matching on digits?

If yes, then try:
=MATCH(--B410,$B$1:$BJ$1,0)

The -- stuff will coerce any text number to a real number. The first minus
converts the text number to a number (but the opposite sign). The second
changes the sign back but keeps the number.


Pas wrote:
>
> Yes the second formula results in #NA. I have a textbox linked to cell "B410"
> , which has a formula. When I clear the formula on that cell it works fine.
> For some weird reason it's giving #NA because the fact that the cell has a
> formula???
> Any ideas?
>
> "Pas" wrote:
>
> > Cheers Dave, I'll try that and let you know how I get on.
> >
> > "Dave Peterson" wrote:
> >
> > > Put these portions of your formula in two other cells:
> > >
> > > =MATCH(B411,$A$2:$A$402,0)
> > > =MATCH(B410,$B$1:$BJ$1,0)
> > >
> > > One or both will return that #N/A error. You'll have to find out why there's a
> > > mismatch (spelling error, too many spaces, ...).
> > >
> > >
> > > Pas wrote:
> > > >
> > > > Does anybody know why I am getting #NA with this formula. It was working OK
> > > > awhile ago????
> > > >
> > > > =INDEX($B$2:$BJ$402,MATCH(B411,$A$2:$A$402,0),MATCH(B410,$B$1:$BJ$1,0))
> > >
> > > --
> > >
> > > Dave Peterson
> > > .
> > >

--

Dave Peterson
 |  Next  |  Last
Pages: 1 2
Prev: How to run a macro automatically when the file is opened?
Next: Allow Users to Edit Ranges