....and I love 'em! most probably because of my "wide-eyed" wonder coupled
with plenty of ignorance that lead me to believe they can do everything (I
know they can't really!), but my spreadsheet task-time is diminishing by the
minute thanks to all you nice and clever people who have taken the time to
answer my longwinded and clumsy attempts at making myself understood and
given me plenty of food for thoughts too!
And no offence taken, David, that was by way of a joke: I understand that
typing a text in Word at the speed of knots is widely different to typing
long formulae in Excell; I get your point completely and thank you again for
all the added info re Excell.

I hope your computer does give you joy today :-)

Kind Regards,
Cat



"David Biddulph" wrote:

I didn't have any lack of faith in your typing ability as such (so my
wording was probably badly chosen), but when reading a formula from the
screen it is often difficult to see where there are and aren't spaces, or to
distinguish them from non-breaking spaces or other such characters, and
similarly it can be difficult to distinguish two single quotes from one
double quote, and the back-quote character ` can add additional confusion.
In some fonts it can also be easy to confuse I, l, 1, or |. Sadly, although
things can often look similar, Excel isn't bright enough to know what was
intended. :-(

I do, however, understand your point that typing things out can help things
to stick in the mind. Glad to hear that the solution worked for you.
[Let's hope that it agrees with what the previous poster had intended!]

.... and sorry that the formatting of the quoted part of the previous message
seems to be getting garbled on some of my messages today. I hate computers!
--
David Biddulph

"CAT" wrote in message
...
Hi David,

Excellent! Works like a dream: no more N/As !!

And thank you for the mini lesson too - very helpful and instructive and I
am absorbing it all like a sponge!
As for the typing comment, I know what you mean, but I pride myself at
being
an excellent typist and I thought that typing it and visually seeing it
unfold would help me understanding what the function was meant to be
doing!

I am very grateful for your time and patience, have a very good day.
Kind Regards
Cat

"David Biddulph" wrote:

Well, you shouldn't be trying (however carefully) to type in a formula
which
is given here. There is always a likelihood of typing errors, so instead
of
reading and typing, just copy the formula from here and paste it into the
formula bar for the relevant cell on your worksheet.

Having said that, the problem in this case wasn't yours, as the formula
given by the previous poster had invalid syntax.
I wonder whether he had intended not to say
=if(ISNA(VLOOKUP(G7,$AM$1:$AN$29,2,FALSE))) but

=IF(ISNA(VLOOKUP(G7,$AM$1:$AN$29,2,FALSE)),"",VLOO KUP(G7,$AM$1:$AN$29,2,FALSE))

When Excel tell you that the formula is invalid, it tries to help by
putting the cursor where the error is, and Excel help for the function in
question will tell you the required syntax (and usually give you examples
and in a "Show Also" link will often show related functions). In this
case your IF function has a condition [the first parameter of the IF],
but it doesn't have the consequence if true [2nd parameter of the IF],
nor does it have the consequence if false [which is the 3rd parameter of
the IF, which is optional in the syntax, but probably a significant
parameter in this case as the 2nd parameter is presumably handling the
error case and you need to handle the non-error case too].
--
David Biddulph

"CAT" wrote in message
...
Hi VMohan,
Thank you very much for taking the time.
I have typed your formula in (several
times, and carefully) but keepgetting "error in formula" by Excell.
Unfortunately, I wouldn't know where theerror
is as I am very much a newbie!
For the record, what I am exactly getting in col R (with no data in col G)
is as follows:
(sign for number (don't have it on the keyboard), then attached to it:N/A.
Does that make any sense?
"vmohan1978" wrote:
use
=if(ISNA(VLOOKUP(G7,$AM$1:$AN$29,2,FALSE)))
"CAT" wrote:
Hi H2fcell,
Thanks a lot; that works great, I am getting exactly what I was
lookingfor
and it's so simple to enter.
Just one thing: If I leave any cell in the G column blank, I am
getting a
//N/A in column R and I wanted it blank, I suppose I could just erase
the
formula, since it does not happen that often and it does not seem to
interfere with the working of it in the following cells.
Comparing to what I had to do before,it's all so simple; thanks
againfor
your help, you've saved me a lot of time.

"h2fcell" wrote:
Hi,
I think you want to change your VLOOKUP to include a reference
tocolumn G
Type below in R7:
=VLOOKUP(G7,$AM$1:$AN$29,2,FALSE)
You can then copy that down column R and the G7 will
changeautomatically to
G8,G9,G10....
Let me know if that's what you're looking for.
....