If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Is Vlookup the correct way to do this?
Hello,
I have a spreadsheet which is basically a database. I want to create a separate worksheet that will fill in with information from this database. What I want to do is enter a number and have it fill in separate cells with info from the corresponding line from the source workbook. I have it setup using Vlookup for each cell after I enter the number from the spreadsheet. It seems to be working fine except for a certain group of numbers. It doesn't always display the info from the source workbook. I have no idea why. I thought I had found some literature from Microsoft that says there is a known issue where it may not display if the source workbook is not open. But it only does this with a certain group of the numbers and not with the others. ?? Any suggestions? |
#2
|
|||
|
|||
Is Vlookup the correct way to do this?
Check very carefully the format of the data and that the source and
destination are the same. In EXCEL 2007 you would do this through:- Home / Font / click on arrow in lower right hand corner / Format Cells should launch. If the souce and destination cells are not consistent with each other this may lead to problems. Please hit Yes if my comments have helped. Thanks. "tammyncook" wrote: Hello, I have a spreadsheet which is basically a database. I want to create a separate worksheet that will fill in with information from this database. What I want to do is enter a number and have it fill in separate cells with info from the corresponding line from the source workbook. I have it setup using Vlookup for each cell after I enter the number from the spreadsheet. It seems to be working fine except for a certain group of numbers. It doesn't always display the info from the source workbook. I have no idea why. I thought I had found some literature from Microsoft that says there is a known issue where it may not display if the source workbook is not open. But it only does this with a certain group of the numbers and not with the others. ?? Any suggestions? |
#3
|
|||
|
|||
Is Vlookup the correct way to do this?
Since you're working with numbers, my bet is that one is really text
masquerading as numbers. So =vlookup() won't find a match. Debra Dalgleish has notes about =vlookup() he http://www.contextures.com/xlFunctions02.html (for =vlookup()) and http://contextures.com/xlFunctions02.html#Trouble On the other hand, you may want to try to automate the process... http://contextures.com/xlForm03.html http://contextures.com/xlForm02.html (also from Deb's site.) tammyncook wrote: Hello, I have a spreadsheet which is basically a database. I want to create a separate worksheet that will fill in with information from this database. What I want to do is enter a number and have it fill in separate cells with info from the corresponding line from the source workbook. I have it setup using Vlookup for each cell after I enter the number from the spreadsheet. It seems to be working fine except for a certain group of numbers. It doesn't always display the info from the source workbook. I have no idea why. I thought I had found some literature from Microsoft that says there is a known issue where it may not display if the source workbook is not open. But it only does this with a certain group of the numbers and not with the others. ?? Any suggestions? -- Dave Peterson |
#4
|
|||
|
|||
Is Vlookup the correct way to do this?
Hi Tammy
It sounds as though the offending group of numbers that do not work are formatted as Text in one case and Numeric in the other. Do you have leading zeros with any of the numbers? If so, then to enter them the cells containing them would have had to be pre-formatted as Text, or the entry would have had to have a single quote '0123 in front of the number to make it a text value. -- Regards Roger Govier "tammyncook" wrote in message ... Hello, I have a spreadsheet which is basically a database. I want to create a separate worksheet that will fill in with information from this database. What I want to do is enter a number and have it fill in separate cells with info from the corresponding line from the source workbook. I have it setup using Vlookup for each cell after I enter the number from the spreadsheet. It seems to be working fine except for a certain group of numbers. It doesn't always display the info from the source workbook. I have no idea why. I thought I had found some literature from Microsoft that says there is a known issue where it may not display if the source workbook is not open. But it only does this with a certain group of the numbers and not with the others. ?? Any suggestions? __________ Information from ESET Smart Security, version of virus signature database 4828 (20100202) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4828 (20100202) __________ The message was checked by ESET Smart Security. http://www.eset.com |
#5
|
|||
|
|||
Is Vlookup the correct way to do this?
Okay. So I've checked to see if the destination & source are formatted the
same. They were not. So I fixed that. BUT... here's the part I don't understand then. They all work to start with. I enter the number I want it to pull the dependant information for. All the info pulls in. I save it and close it, then some time later, I will be notified that it's not pulling it in. Sure enough I go look and all my values are at #N/A as if I never had entered the number to begin with. Will it behave this way due to the formatting?? Thanks, |
#6
|
|||
|
|||
Is Vlookup the correct way to do this?
If your entries are digits, then it's not enough to just change the format of
the cells. You'll want to make the values numeric, too. I like to find an empty cell Edit|copy Select the offending cells Edit|paste special|add This will coerce the "text numbers" to "number numbers". You may need to do this to both ranges--the values you're matching and the values in the first column of the vlookup range. But that doesn't explain why your values (and formatting(?)) change when you close and reopen the workbook. Is the data static or is it being retrieved from some other source (maybe refreshed when the workbook opens???)? If you open the workbook with macros disabled, does the problem go away? tammyncook wrote: Okay. So I've checked to see if the destination & source are formatted the same. They were not. So I fixed that. BUT... here's the part I don't understand then. They all work to start with. I enter the number I want it to pull the dependant information for. All the info pulls in. I save it and close it, then some time later, I will be notified that it's not pulling it in. Sure enough I go look and all my values are at #N/A as if I never had entered the number to begin with. Will it behave this way due to the formatting?? Thanks, -- Dave Peterson |
#7
|
|||
|
|||
Is Vlookup the correct way to do this?
Data is being retrieved from a separate source. The workbook with the
destination cells asks to update every time it is opened. No macros though. "Dave Peterson" wrote: If your entries are digits, then it's not enough to just change the format of the cells. You'll want to make the values numeric, too. I like to find an empty cell Edit|copy Select the offending cells Edit|paste special|add This will coerce the "text numbers" to "number numbers". You may need to do this to both ranges--the values you're matching and the values in the first column of the vlookup range. But that doesn't explain why your values (and formatting(?)) change when you close and reopen the workbook. Is the data static or is it being retrieved from some other source (maybe refreshed when the workbook opens???)? If you open the workbook with macros disabled, does the problem go away? tammyncook wrote: Okay. So I've checked to see if the destination & source are formatted the same. They were not. So I fixed that. BUT... here's the part I don't understand then. They all work to start with. I enter the number I want it to pull the dependant information for. All the info pulls in. I save it and close it, then some time later, I will be notified that it's not pulling it in. Sure enough I go look and all my values are at #N/A as if I never had entered the number to begin with. Will it behave this way due to the formatting?? Thanks, -- Dave Peterson . |
#8
|
|||
|
|||
Is Vlookup the correct way to do this?
It sounds like the refreshed data doesn't come in the way you like.
If the values are coming in as text and your table has real numbers, you could use: =vlookup(--a1,sheet2!a:e,2,false) the -- stuff will coerce text that look like numbers into numbers. (It'll cause an error if you have real non-digits in A1.) If the values are coming in as numbers and your table has text numbers, you could use: =vlookup(""&a1,sheet2!a:e,2,false) or =vlookup(text(a1,"000000"),sheet2!a:e,2,false) But if it were my project, I'd fix my table to match the values (not just format!) of the incoming data. I think it's easier to fix the table than to remember how to modify formulas each time I need an =vlookup(). tammyncook wrote: Data is being retrieved from a separate source. The workbook with the destination cells asks to update every time it is opened. No macros though. "Dave Peterson" wrote: If your entries are digits, then it's not enough to just change the format of the cells. You'll want to make the values numeric, too. I like to find an empty cell Edit|copy Select the offending cells Edit|paste special|add This will coerce the "text numbers" to "number numbers". You may need to do this to both ranges--the values you're matching and the values in the first column of the vlookup range. But that doesn't explain why your values (and formatting(?)) change when you close and reopen the workbook. Is the data static or is it being retrieved from some other source (maybe refreshed when the workbook opens???)? If you open the workbook with macros disabled, does the problem go away? tammyncook wrote: Okay. So I've checked to see if the destination & source are formatted the same. They were not. So I fixed that. BUT... here's the part I don't understand then. They all work to start with. I enter the number I want it to pull the dependant information for. All the info pulls in. I save it and close it, then some time later, I will be notified that it's not pulling it in. Sure enough I go look and all my values are at #N/A as if I never had entered the number to begin with. Will it behave this way due to the formatting?? Thanks, -- Dave Peterson . -- Dave Peterson |
#9
|
|||
|
|||
Is Vlookup the correct way to do this?
Can I send you screen shots or my spreadsheet so you can see what I've got?
I'm not sure I'm communicating properly... "Dave Peterson" wrote: It sounds like the refreshed data doesn't come in the way you like. If the values are coming in as text and your table has real numbers, you could use: =vlookup(--a1,sheet2!a:e,2,false) the -- stuff will coerce text that look like numbers into numbers. (It'll cause an error if you have real non-digits in A1.) If the values are coming in as numbers and your table has text numbers, you could use: =vlookup(""&a1,sheet2!a:e,2,false) or =vlookup(text(a1,"000000"),sheet2!a:e,2,false) But if it were my project, I'd fix my table to match the values (not just format!) of the incoming data. I think it's easier to fix the table than to remember how to modify formulas each time I need an =vlookup(). tammyncook wrote: Data is being retrieved from a separate source. The workbook with the destination cells asks to update every time it is opened. No macros though. "Dave Peterson" wrote: If your entries are digits, then it's not enough to just change the format of the cells. You'll want to make the values numeric, too. I like to find an empty cell Edit|copy Select the offending cells Edit|paste special|add This will coerce the "text numbers" to "number numbers". You may need to do this to both ranges--the values you're matching and the values in the first column of the vlookup range. But that doesn't explain why your values (and formatting(?)) change when you close and reopen the workbook. Is the data static or is it being retrieved from some other source (maybe refreshed when the workbook opens???)? If you open the workbook with macros disabled, does the problem go away? tammyncook wrote: Okay. So I've checked to see if the destination & source are formatted the same. They were not. So I fixed that. BUT... here's the part I don't understand then. They all work to start with. I enter the number I want it to pull the dependant information for. All the info pulls in. I save it and close it, then some time later, I will be notified that it's not pulling it in. Sure enough I go look and all my values are at #N/A as if I never had entered the number to begin with. Will it behave this way due to the formatting?? Thanks, -- Dave Peterson . -- Dave Peterson . |
#10
|
|||
|
|||
Is Vlookup the correct way to do this?
No thanks.
Describe your situation in plain text in the newsgroup. You'll have lots of eyeballs to help. tammyncook wrote: Can I send you screen shots or my spreadsheet so you can see what I've got? I'm not sure I'm communicating properly... "Dave Peterson" wrote: It sounds like the refreshed data doesn't come in the way you like. If the values are coming in as text and your table has real numbers, you could use: =vlookup(--a1,sheet2!a:e,2,false) the -- stuff will coerce text that look like numbers into numbers. (It'll cause an error if you have real non-digits in A1.) If the values are coming in as numbers and your table has text numbers, you could use: =vlookup(""&a1,sheet2!a:e,2,false) or =vlookup(text(a1,"000000"),sheet2!a:e,2,false) But if it were my project, I'd fix my table to match the values (not just format!) of the incoming data. I think it's easier to fix the table than to remember how to modify formulas each time I need an =vlookup(). tammyncook wrote: Data is being retrieved from a separate source. The workbook with the destination cells asks to update every time it is opened. No macros though. "Dave Peterson" wrote: If your entries are digits, then it's not enough to just change the format of the cells. You'll want to make the values numeric, too. I like to find an empty cell Edit|copy Select the offending cells Edit|paste special|add This will coerce the "text numbers" to "number numbers". You may need to do this to both ranges--the values you're matching and the values in the first column of the vlookup range. But that doesn't explain why your values (and formatting(?)) change when you close and reopen the workbook. Is the data static or is it being retrieved from some other source (maybe refreshed when the workbook opens???)? If you open the workbook with macros disabled, does the problem go away? tammyncook wrote: Okay. So I've checked to see if the destination & source are formatted the same. They were not. So I fixed that. BUT... here's the part I don't understand then. They all work to start with. I enter the number I want it to pull the dependant information for. All the info pulls in. I save it and close it, then some time later, I will be notified that it's not pulling it in. Sure enough I go look and all my values are at #N/A as if I never had entered the number to begin with. Will it behave this way due to the formatting?? Thanks, -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|