vlookup

[Guest]

I have a workbook with 3 pages. One is a form, one is a job list, and the
last one is a vendor list. On the form, I have two cells I fill with vendor #
& job # information, which I want to fill in below in other cells of the
form. I can get one of the vlookup formulas to work, the other one comes back
with either the wrong job location or #N/A. My formula looks like this:
=VLOOKUP(L1,'Job List'!$A$1:$AY$9999,2,FALSE).
My job list and vendor lists are simply two columns each; identifying number
in column A, and Vendor name/Job location in Column B. Help?

 

[Macgru]

I have a workbook with 3 pages. One is a form, one is a job list, and the
last one is a vendor list. On the form, I have two cells I fill with vendor #
& job # information, which I want to fill in below in other cells of the
form. I can get one of the vlookup formulas to work, the other one comes back
with either the wrong job location or #N/A. My formula looks like this:
=VLOOKUP(L1,'Job List'!$A$1:$AY$9999,2,FALSE).
My job list and vendor lists are simply two columns each; identifying number
in column A, and Vendor name/Job location in Column B. Help?

maybe
check if L1 and job list/vendor have the same format
check if somme extra spaces occurs in L1/ job list/vendor
mcg

 

[Guest]

The two formulas are exactly the same, the only differences are the cell
reference, and the sheet name. It still doesn't work.

 

[Macgru]

The two formulas are exactly the same, the only differences are the cell
reference, and the sheet name. It still doesn't work.

formula should look L1 in other sheets?
if so try to put $L$1 instead of L1
mcg

 

[Guest]

No. Cell L1 on the Form page contains the job #. When I enter the job #, the
job location should fill in D21. This information comes from the sheet called
"Job List".

 

[JulieD]

Hi HR

just checking is the VLOOKUP formula in cell D21 of the form page? and you
want to look up the value in L1 of the form page, in the Job List sheet and
return the value from column B of this sheet.
if so, and the format of L1 and Job List column A are identical then your
formula
=VLOOKUP(L1,'Job List'!$A$1:$AY$9999,2,FALSE).
should work

therefore i'ld check for leading or trailing spaces either around the data
in L1 or the data in column A of the Job List sheet. OR after the name in
the sheet tab (often been caught with a trailing space here)

if this doesn't work, email me direct with your workbook and i'll check it
out (julied_ng at hcts dot net dot au)

Cheers
JulieD

 

[Guest]

I e-mailed the book to you. Any help would be greatly appreciated! Thanks in
advance.

 

[JulieD]

Hi Heather

got the workbook (i'm posting the solution here for others who may be
following this thread) ...
.... on the first sheet L1 the data you've entered 4134 which when you run a
=ISNUMBER(L1) test on it returns TRUE in column A of the JobList sheet
however, you have 04134 entered which when you run a =ISNUMBER(A287) test on
it returns FALSE, therefore you're comparing a number value of
4134 with a text value of 04134, which is why it is not working. The
misleading thing here is that unless you run a ISNUMBER test on them you
can't tell that they're not the same, as Excel is showing "general" for both
their formats.

There is an additional problem also with the data in column A of the
JobList, A287, for example has a space after the 4 which means that the
actual contents of the cell is 04134<space> and not 04134 - actually, to
make matters worse, it's not a true space as neither the TRIM nor the CLEAN
function fixed the problem ... i had to run David McRitchie's TrimAll macro
over it to fix it - and then you lost your leading zeros, so i had to use
the TEXT fuction to put them back!!! (i'm email you the "fixed up" list
back)

Therefore, you have basically 3 options:
1) create a drop down list in L1 which takes its values directly from column
A of the JOBLIST sheet and then use this list to find & populate L1 with the
value you're interested in. Debra Dalgleish has notes on using Data
Validation for this purpose on her website at
www.contextures.com/tiptech.html ...however, as you have a rather long list
of items in A and Data / Validation lists aren't predicitive (ie you can't
type and the list will go to that area), i'm not sure how this will work for
you.

2) format L1 as text, remember to enter the values with a leading 0's

3) use a combo box (drop down box) control from the control toolbox to
populate this cell ... which is what i've done on your workbook - these are
predictive and will solve your problems (i hope)

Let me know how you go.

Cheers
JulieD