Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|