VLookup pasted data

[Mick]

Hi,

I am copying and pasting data from one sheet to another. When the Vlookup
formula is run it does not pick up the copied and pasted cells-but it will
pick the typed text.....Is there a way around this?

Thanks

 

[pdberger]

Mick --

Is the data you copied and pasted formatted as text, but you're typing in
numeric values? If you're getting a #N/A returned, that might be a cause.

HTH

 

[Mick]

Hi there,

Yes it is formatted at text- When I delete the pasted text and type in the
word it works.....

 

[pdberger]

I can't tell from your response whether that's what you want to be doing. If
it were me, I wouldn't want to do it. Post an example, and maybe we can
figure it out...

 

[Mick]

Hey PD-

OK- What I have is an xls file with 2 worksheets (200 and availability) .
The formula I'm using is:

=vlookup($b3:$b202,availability!$A2:$B437,2,False)

The data in 200 is static. The data in availability is copied and pasted
from another xls file in to the availability workbook. If the data is typed
in to availability the formula works, otherwise it comes up with #N/A.

Hope this is clearer!!!

Thanks for your help

Mick

 

[pdberger]

Mick --

It sounds as if the info you're copying and pasting from another workbook is
numeric but pasting in as text. But it sounds like you want it to be
numeric. If that's the case, try this. Instead of a simple paste, do a
Paste Special, clicking the 'Add' option. This process will add the range to
the values already in the cells. Since they're empty, it'll add to zero.
However, it'll also convert them to numeric values.

HTH