I have a report that gets updated monthly. Some info comes
in the report, the rest I have to put in.

I take my original report and copy it to two worksheets
within the workbook (Original, Changes, Inventory). On
one sheet (Inventory), I can vlookup information from the
previous month's report, and it fills it in. Vlookup
doesn't work on the other sheet (Changes), even though
there are no formatting differences, using same report
from previous month.

Col a = company number
col's b-h need to be filled in identically to all other
months based on the company number (column mapping is the
same).
Is there an IF function that would do this ? Or why will
vlookup work on the other sheet & not this one?

Jim,
I did a replace of any/all blanks (up to 4 spaces) and the
vlookup still returns a #N/A. The problem I have with
this is that I took the original sheet & copied to the
Inventory & Changes. I made no changes to either, and am
attempting the exact same thing on both, but it only works
on one!
Is there some kind of formula that will look up from one
sheet to the other & populate the next few cells?
Thanks - Jodi


-----Original Message-----
The info that comes in the report I assume is a dump out
of some accounting
system or another. Quite often when data is dumped out it
will have trailing
blank characters. Vlookup will not find exact matches
because of the trailing
blanks. Do a find and replace on the blank, replacing
with nothing. Vlookup
won't match "Dog" in one cell with "Dog " in another...

If you need to maintain some blank characters in the
middle of the strings
then you will need to write a quick little macro that
Trims the blanks from
the end. This can also be useful if this is something
that you will need to
do on a regular basis... I fyou need help with something
like this post a
reply.

Hope this works for you...

"Jodi" wrote:

I have a report that gets updated monthly. Some info
comes
in the report, the rest I have to put in.

I take my original report and copy it to two worksheets
within the workbook (Original, Changes, Inventory). On
one sheet (Inventory), I can vlookup information from
the
previous month's report, and it fills it in. Vlookup
doesn't work on the other sheet (Changes), even though
there are no formatting differences, using same report
from previous month.

Col a = company number
col's b-h need to be filled in identically to all other
months based on the company number (column mapping is
the
same).
Is there an IF function that would do this ? Or why
will
vlookup work on the other sheet & not this one?


.

Ok, Jim.
I went to the current sheet and changed the format to
currency - decimals & $ showed up. Then I went to last
months report & did the same thing - nothing. Then I went
to this month's report that was already vlookup'd - and
nothing again!
I have no idea what a string is, but you said it won't
work. However, it worked on another sheet in the same
workbook!! Therein lies my confusion.
Any way to change the string so that its not a string??
Thank you SOOOO much for your help!!!
Jodi

-----Original Message-----
Wierd... A few more things to try... Is the data that you
are looking up all
numeric. If so then it is possible that one instace is a
number and the other
is a string. You can check this by changing the format on
the cell to comma
or dollars. If it won't format then it is a string. If it
will then it is
numeric... Vlookup crashes trying to look up a number in
a group of strings
or vice versa...

Other wise try this. Change the range that you are
looking up to a named
range. Instead of =vlookup(A2, $C$1:$E$100, 2, false) set

$C$1:$E$100 as a named range called Inventory or
whatever...

Change the fromula to

=vlookup(A2, Inventory , 2, false)

If this won't work then let me know. By the way named
ranges are in my
opinion always better for Vlookups than Cell references,
because they are
absolute and the formula will read like English instead
of like obscure
references...

let me know how this works...

"Jodi" wrote:

Jim,
I did a replace of any/all blanks (up to 4 spaces) and
the
vlookup still returns a #N/A. The problem I have with
this is that I took the original sheet & copied to the
Inventory & Changes. I made no changes to either, and
am
attempting the exact same thing on both, but it only
works
on one!
Is there some kind of formula that will look up from
one
sheet to the other & populate the next few cells?
Thanks - Jodi


-----Original Message-----
The info that comes in the report I assume is a dump
out
of some accounting
system or another. Quite often when data is dumped out
it
will have trailing
blank characters. Vlookup will not find exact matches
because of the trailing
blanks. Do a find and replace on the blank, replacing
with nothing. Vlookup
won't match "Dog" in one cell with "Dog " in
another...

If you need to maintain some blank characters in the
middle of the strings
then you will need to write a quick little macro that
Trims the blanks from
the end. This can also be useful if this is something
that you will need to
do on a regular basis... I fyou need help with
something
like this post a
reply.

Hope this works for you...

"Jodi" wrote:

I have a report that gets updated monthly. Some info
comes
in the report, the rest I have to put in.

I take my original report and copy it to two
worksheets
within the workbook (Original, Changes, Inventory).
On
one sheet (Inventory), I can vlookup information
from
the
previous month's report, and it fills it in.
Vlookup
doesn't work on the other sheet (Changes), even
though
there are no formatting differences, using same
report
from previous month.

Col a = company number
col's b-h need to be filled in identically to all
other
months based on the company number (column mapping
is
the
same).
Is there an IF function that would do this ? Or why
will
vlookup work on the other sheet & not this one?


.


.

Hi Jim - got my answer from the knowledge base (Use Paste
Special with Multiply).
THANK YOU SO MUCH FOR YOUR HELP!!!!



-----Original Message-----
Wierd...

Are the items you are looking up numeric. If so it is
possible that when you
copy and paste data that the strings are changed to
values. Check to see if
the cells are numbers or not by formatting them as
dollars. if they won't
format they are strings. Vlookup is the formula you
want, but it will crash
if you are trying to match 12345 with "12345".

Data output from a system sometimes comes into
spreadsheets as strings by
default instead of numbers. If this is the problem there
are a couple of easy
fixes. Let me know...

"Jodi" wrote:

Jim,
I did a replace of any/all blanks (up to 4 spaces) and
the
vlookup still returns a #N/A. The problem I have with
this is that I took the original sheet & copied to the
Inventory & Changes. I made no changes to either, and
am
attempting the exact same thing on both, but it only
works
on one!
Is there some kind of formula that will look up from
one
sheet to the other & populate the next few cells?
Thanks - Jodi


-----Original Message-----
The info that comes in the report I assume is a dump
out
of some accounting
system or another. Quite often when data is dumped out
it
will have trailing
blank characters. Vlookup will not find exact matches
because of the trailing
blanks. Do a find and replace on the blank, replacing
with nothing. Vlookup
won't match "Dog" in one cell with "Dog " in
another...

If you need to maintain some blank characters in the
middle of the strings
then you will need to write a quick little macro that
Trims the blanks from
the end. This can also be useful if this is something
that you will need to
do on a regular basis... I fyou need help with
something
like this post a
reply.

Hope this works for you...

"Jodi" wrote:

I have a report that gets updated monthly. Some info
comes
in the report, the rest I have to put in.

I take my original report and copy it to two
worksheets
within the workbook (Original, Changes, Inventory).
On
one sheet (Inventory), I can vlookup information
from
the
previous month's report, and it fills it in.
Vlookup
doesn't work on the other sheet (Changes), even
though
there are no formatting differences, using same
report
from previous month.

Col a = company number
col's b-h need to be filled in identically to all
other
months based on the company number (column mapping
is
the
same).
Is there an IF function that would do this ? Or why
will
vlookup work on the other sheet & not this one?


.


.