Is Vlookup the correct way to do this?

[tammyncook]

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?

 

[trip_to_tokyo]

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.

 

[Dave Peterson]

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() here:
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.)

 

[Roger Govier]

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

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

 

[tammyncook]

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]

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]

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]

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]

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]

No thanks.

Describe your situation in plain text in the newsgroup. You'll have lots of
eyeballs to help.