Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
hrsdias
 
Posts: n/a
Default Vlookup and #N/A error


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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Arvi Laanemets
 
Posts: n/a
Default Vlookup and #N/A error

Hi

Instead CopyPaste use CopyPasteSpecial Values


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"hrsdias" wrote in
message ...

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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
hrsdias
 
Posts: n/a
Default Vlookup and #N/A error


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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Niek Otten
 
Posts: n/a
Default Vlookup and #N/A error

<but it doesn't work

So what *does* it do?

--
Kind regards,

Niek Otten

"hrsdias" wrote in
message ...

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



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
hrsdias
 
Posts: n/a
Default Vlookup and #N/A error


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" wrote
in
message ...

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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Niek Otten
 
Posts: n/a
Default Vlookup and #N/A error

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" wrote in
message ...

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" wrote
in
message ...

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



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Patti
 
Posts: n/a
Default Vlookup and #N/A error

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 02:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"