Hi Super clever coders
I would love to have some VB code that I could put into a spreadsheet (or
another clever way to do this) that will return a 0 whenever a VLOOKUP
formula returns a vaue of #N/A. I use VLOOPUP to return values into a column
which is then totalled using sum or subtotal, but these formulas won't work
because of the #N/As returned.
Any help woul dbe much appreceiated.

Marie -

Try something like this:

=IF(ISNA(<lookup formula),<value to return if N/A,<lookup formula)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Marie Bayes" wrote in message
...
Hi Super clever coders
I would love to have some VB code that I could put into a spreadsheet (or
another clever way to do this) that will return a 0 whenever a VLOOKUP
formula returns a vaue of #N/A. I use VLOOPUP to return values into a
column
which is then totalled using sum or subtotal, but these formulas won't
work
because of the #N/As returned.
Any help woul dbe much appreceiated.

I've tried this but for some reason it's not liked, it doesn't like the value
I'm trying to return if NA (which is 0, so nothing too complicated), could I
be doing something very wrong, this is the formula as I've typed it..
=IF(ISNA(VLOOKUP($E7,'[lead gen campaign cumulative january by source
code.xls]Sheet1'!$A:$G,3,FALSE),0,VLOOKUP($E7,'[lead gen campaign cumulative
january by source code.xls]Sheet1'!$A:$G,3,FALSE)

"Jon Peltier" wrote:

Marie -

Try something like this:

=IF(ISNA(<lookup formula),<value to return if N/A,<lookup formula)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Marie Bayes" wrote in message
...
Hi Super clever coders
I would love to have some VB code that I could put into a spreadsheet (or
another clever way to do this) that will return a 0 whenever a VLOOKUP
formula returns a vaue of #N/A. I use VLOOPUP to return values into a
column
which is then totalled using sum or subtotal, but these formulas won't
work
because of the #N/As returned.
Any help woul dbe much appreceiated.

Sorry, don't know if you can still help, I've amended the formula from my
last reply to this (there was a close bracket missing): it now appears to
work but returns the wrong value:
=IF(ISNA(VLOOKUP($E7,'[lead gen campaign cumulative january by source
code.xls]Sheet1'!$A:$G,3,FALSE)),0,VLOOKUP($E7,'[lead gen campaign cumulative
january by source code.xls]Sheet1'!$A:$G,3,FALSE))

"Jon Peltier" wrote:

Marie -

Try something like this:

=IF(ISNA(<lookup formula),<value to return if N/A,<lookup formula)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Marie Bayes" wrote in message
...
Hi Super clever coders
I would love to have some VB code that I could put into a spreadsheet (or
another clever way to do this) that will return a 0 whenever a VLOOKUP
formula returns a vaue of #N/A. I use VLOOPUP to return values into a
column
which is then totalled using sum or subtotal, but these formulas won't
work
because of the #N/As returned.
Any help woul dbe much appreceiated.

Ignore all previous, i#ve corrected my stupid mistakes and now it all works
perfectly, thanks so much.

"Jon Peltier" wrote:

Marie -

Try something like this:

=IF(ISNA(<lookup formula),<value to return if N/A,<lookup formula)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Marie Bayes" wrote in message
...
Hi Super clever coders
I would love to have some VB code that I could put into a spreadsheet (or
another clever way to do this) that will return a 0 whenever a VLOOKUP
formula returns a vaue of #N/A. I use VLOOPUP to return values into a
column
which is then totalled using sum or subtotal, but these formulas won't
work
because of the #N/As returned.
Any help woul dbe much appreceiated.

Make sure that calculation is set to automatic--just in case.
Tools|options|calculation tab

But then look at your data once more. I bet you'll find that match earlier than
you think--maybe on a hidden row???

Marie Bayes wrote:

Sorry, don't know if you can still help, I've amended the formula from my
last reply to this (there was a close bracket missing): it now appears to
work but returns the wrong value:
=IF(ISNA(VLOOKUP($E7,'[lead gen campaign cumulative january by source
code.xls]Sheet1'!$A:$G,3,FALSE)),0,VLOOKUP($E7,'[lead gen campaign cumulative
january by source code.xls]Sheet1'!$A:$G,3,FALSE))

"Jon Peltier" wrote:

Marie -

Try something like this:

=IF(ISNA(<lookup formula),<value to return if N/A,<lookup formula)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Marie Bayes" wrote in message
...
Hi Super clever coders
I would love to have some VB code that I could put into a spreadsheet (or
another clever way to do this) that will return a 0 whenever a VLOOKUP
formula returns a vaue of #N/A. I use VLOOPUP to return values into a
column
which is then totalled using sum or subtotal, but these formulas won't
work
because of the #N/As returned.
Any help woul dbe much appreceiated.




--

Dave Peterson

Thanks Dave, as you might have noticed, all was present and correct and it
was just me making a mistake!

"Dave Peterson" wrote:

Make sure that calculation is set to automatic--just in case.
Tools|options|calculation tab

But then look at your data once more. I bet you'll find that match earlier than
you think--maybe on a hidden row???

Marie Bayes wrote:

Sorry, don't know if you can still help, I've amended the formula from my
last reply to this (there was a close bracket missing): it now appears to
work but returns the wrong value:
=IF(ISNA(VLOOKUP($E7,'[lead gen campaign cumulative january by source
code.xls]Sheet1'!$A:$G,3,FALSE)),0,VLOOKUP($E7,'[lead gen campaign cumulative
january by source code.xls]Sheet1'!$A:$G,3,FALSE))

"Jon Peltier" wrote:

Marie -

Try something like this:

=IF(ISNA(<lookup formula),<value to return if N/A,<lookup formula)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Marie Bayes" wrote in message
...
Hi Super clever coders
I would love to have some VB code that I could put into a spreadsheet (or
another clever way to do this) that will return a 0 whenever a VLOOKUP
formula returns a vaue of #N/A. I use VLOOPUP to return values into a
column
which is then totalled using sum or subtotal, but these formulas won't
work
because of the #N/As returned.
Any help woul dbe much appreceiated.




--

Dave Peterson

Your other post hadn't arrived through the internets to me--I think the tubes
were clogged!

Marie Bayes wrote:

Thanks Dave, as you might have noticed, all was present and correct and it
was just me making a mistake!

"Dave Peterson" wrote:

Make sure that calculation is set to automatic--just in case.
Tools|options|calculation tab

But then look at your data once more. I bet you'll find that match earlier than
you think--maybe on a hidden row???

Marie Bayes wrote:

Sorry, don't know if you can still help, I've amended the formula from my
last reply to this (there was a close bracket missing): it now appears to
work but returns the wrong value:
=IF(ISNA(VLOOKUP($E7,'[lead gen campaign cumulative january by source
code.xls]Sheet1'!$A:$G,3,FALSE)),0,VLOOKUP($E7,'[lead gen campaign cumulative
january by source code.xls]Sheet1'!$A:$G,3,FALSE))

"Jon Peltier" wrote:

Marie -

Try something like this:

=IF(ISNA(<lookup formula),<value to return if N/A,<lookup formula)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Marie Bayes" wrote in message
...
Hi Super clever coders
I would love to have some VB code that I could put into a spreadsheet (or
another clever way to do this) that will return a 0 whenever a VLOOKUP
formula returns a vaue of #N/A. I use VLOOPUP to return values into a
column
which is then totalled using sum or subtotal, but these formulas won't
work
because of the #N/As returned.
Any help woul dbe much appreceiated.




--

Dave Peterson


--

Dave Peterson