Vlookup and #N/A error

[hrsdias]

I keep getting this problem and always find very hard to overcome without loosing much time and efforts. I have one spreadsheet A with vlookup formulas that are supposed to retrieve the info I want (prices) which are located in another spredsheet (B). The idea is to insert a reference in spreadsheet A and the vlookup formula gives me the price for that reference. Every year I have prices updates so I copy the spreadsheet that I am given from my boss with the thousands of new prices and paste it to the spreadsheet B. The problem is that once I do this the vlookup formulas in spreasheet A seems to not recognize the values I've just pasted in spreadheet B and retrieve no values! A friend of mine once teached me to use a formula to format the cells in spreadheet B. This is: "=D4&"" (D4 is just an example of a cell - I do this to every cells). I insert new columns and paste this formula in there and then I copy and paste the values to the cells where they should be. Once I do this the vlookup formula starts to work and retrieve the values that I want but I loose the format of the cells - I have columns which I need to be formated as currency with only 2 decimal places (I get 10!), others that are percentile, and so on. Is there an easy way to paste the data in spreadsheet B and immediately the formulas in spreadheet A reterieve the values I want and with the format I want? Thanks in advance.

[Arvi Laanemets]

Hi

Instead Copy>Paste use Copy>PasteSpecial Values


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )



"hrsdias" <[email protected]> wrote in
message news:[email protected]...
>
> I keep getting this problem and always find very hard to overcome
> without loosing much time and efforts. I have one spreadsheet A with
> vlookup formulas that are supposed to retrieve the info I want (prices)
> which are located in another spredsheet (B). The idea is to insert a
> reference in spreadsheet A and the vlookup formula gives me the price
> for that reference. Every year I have prices updates so I copy the
> spreadsheet that I am given from my boss with the thousands of new
> prices and paste it to the spreadsheet B. The problem is that once I do
> this the vlookup formulas in spreasheet A seems to not recognize the
> values I've just pasted in spreadheet B and retrieve no values! A
> friend of mine once teached me to use a formula to format the cells in
> spreadheet B. This is: "=D4&"" (D4 is just an example of a cell - I do
> this to every cells). I insert new columns and paste this formula in
> there and then I copy and paste the values to the cells where they
> should be. Once I do this the vlookup formula starts to work and
> retrieve the values that I want but I loose the format of the cells - I
> have columns which I need to be formated as currency with only 2 decimal
> places (I get 10!), others that are percentile, and so on. Is there an
> easy way to paste the data in spreadsheet B and immediately the
> formulas in spreadheet A reterieve the values I want and with the
> format I want? Thanks in advance.
>
>
> --
> hrsdias
> ------------------------------------------------------------------------
> hrsdias's Profile:
> http://www.excelforum.com/member.php...o&userid=29919
> View this thread: http://www.excelforum.com/showthread...hreadid=496146
>

[hrsdias]

I wish it would be that easy! I tried but it doesn't work. I think I have to format the cells before or after I paste the new values. I just don't know how I should do it. Any other suggestions?

[Niek Otten]

<but it doesn't work>

So what *does* it do?

--
Kind regards,

Niek Otten

"hrsdias" <[email protected]> wrote in
message news:[email protected]...
>
> I wish it would be that easy! I tried but it doesn't work. I think I
> have to format the cells before or after I paste the new values. I just
> don't know how I should do it. Any other suggestions?
>
>
> --
> hrsdias
> ------------------------------------------------------------------------
> hrsdias's Profile:
> http://www.excelforum.com/member.php...o&userid=29919
> View this thread: http://www.excelforum.com/showthread...hreadid=496146
>

[hrsdias]

It doesn't do anything. The Vlookup formulas in the spreadsheet A won't retrieve the values!

<but it doesn't work>

So what *does* it do?

--
Kind regards,

Niek Otten

"hrsdias" <[email protected]> wrote in
message news:[email protected]...
>
> I wish it would be that easy! I tried but it doesn't work. I think I
> have to format the cells before or after I paste the new values. I just
> don't know how I should do it. Any other suggestions?
>
>
> --
> hrsdias
> ------------------------------------------------------------------------
> hrsdias's Profile:
> http://www.excelforum.com/member.php...o&userid=29919
> View this thread: http://www.excelforum.com/showthread...hreadid=496146
>

[Niek Otten]

So what do you see? An error value? What? The formula? Then format the cell
as general and re-enter the formula (F2, ENTER)
"Doesn't do" doesn't exist! Or is there a whole in your screen where look
through?

--
Kind regards,

Niek Otten

"hrsdias" <[email protected]> wrote in
message news:[email protected]...
>
> It doesn't do anything. The Vlookup formulas in the spreadsheet A won't
> retrieve the values!
>
>
> Niek Otten Wrote:
>> <but it doesn't work>
>>
>> So what *does* it do?
>>
>> --
>> Kind regards,
>>
>> Niek Otten
>>
>> "hrsdias" <[email protected]> wrote
>> in
>> message news:[email protected]...
>> >
>> > I wish it would be that easy! I tried but it doesn't work. I think I
>> > have to format the cells before or after I paste the new values. I
>> just
>> > don't know how I should do it. Any other suggestions?
>> >
>> >
>> > --
>> > hrsdias
>> >
>> ------------------------------------------------------------------------
>> > hrsdias's Profile:
>> > http://www.excelforum.com/member.php...o&userid=29919
>> > View this thread:
>> http://www.excelforum.com/showthread...hreadid=496146
>> >
>
>
> --
> hrsdias
> ------------------------------------------------------------------------
> hrsdias's Profile:
> http://www.excelforum.com/member.php...o&userid=29919
> View this thread: http://www.excelforum.com/showthread...hreadid=496146
>

[Patti]

Have you tried using "Data -- Text to Columns -- Finish" to eliminate extra
spaces?


"hrsdias" wrote:

>
> I keep getting this problem and always find very hard to overcome
> without loosing much time and efforts. I have one spreadsheet A with
> vlookup formulas that are supposed to retrieve the info I want (prices)
> which are located in another spredsheet (B). The idea is to insert a
> reference in spreadsheet A and the vlookup formula gives me the price
> for that reference. Every year I have prices updates so I copy the
> spreadsheet that I am given from my boss with the thousands of new
> prices and paste it to the spreadsheet B. The problem is that once I do
> this the vlookup formulas in spreasheet A seems to not recognize the
> values I've just pasted in spreadheet B and retrieve no values! A
> friend of mine once teached me to use a formula to format the cells in
> spreadheet B. This is: "=D4&"" (D4 is just an example of a cell - I do
> this to every cells). I insert new columns and paste this formula in
> there and then I copy and paste the values to the cells where they
> should be. Once I do this the vlookup formula starts to work and
> retrieve the values that I want but I loose the format of the cells - I
> have columns which I need to be formated as currency with only 2 decimal
> places (I get 10!), others that are percentile, and so on. Is there an
> easy way to paste the data in spreadsheet B and immediately the
> formulas in spreadheet A reterieve the values I want and with the
> format I want? Thanks in advance.
>
>
> --
> hrsdias
> ------------------------------------------------------------------------
> hrsdias's Profile: http://www.excelforum.com/member.php...o&userid=29919
> View this thread: http://www.excelforum.com/showthread...hreadid=496146
>
>