vlookup- wrong job location-Vendor name/Job location

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?

U¿ytkownik "HR" <[email protected]> napisa³ w wiadomo¶ci
news:[email protected]...
> 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

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

"HR" wrote:

> 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?

U¿ytkownik "HR" <[email protected]> napisa³ w wiadomo¶ci
news:[email protected]...
> The two formulas are exactly the same, the only differences are the cell
> reference, and the sheet name. It still doesn't work.
>
> "HR" wrote:
>
> > 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?

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

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".

"Macgru" wrote:

>
> U¿ytkownik "HR" <[email protected]> napisa³ w wiadomo¶ci
> news:[email protected]...
> > The two formulas are exactly the same, the only differences are the cell
> > reference, and the sheet name. It still doesn't work.
> >
> > "HR" wrote:
> >
> > > 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?
>
> formula should look L1 in other sheets?
> if so try to put $L$1 instead of L1
> mcg
>
>
>

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


"HR" <[email protected]> wrote in message
news:[email protected]...
> 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".
>
> "Macgru" wrote:
>
>>
>> U¿ytkownik "HR" <[email protected]> napisa³ w wiadomo¶ci
>> news:[email protected]...
>> > The two formulas are exactly the same, the only differences are the
>> > cell
>> > reference, and the sheet name. It still doesn't work.
>> >
>> > "HR" wrote:
>> >
>> > > 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?
>>
>> formula should look L1 in other sheets?
>> if so try to put $L$1 instead of L1
>> mcg
>>
>>
>>

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

"JulieD" wrote:

> 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
>
>
> "HR" <[email protected]> wrote in message
> news:[email protected]...
> > 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".
> >
> > "Macgru" wrote:
> >
> >>
> >> U¿ytkownik "HR" <[email protected]> napisa³ w wiadomo¶ci
> >> news:[email protected]...
> >> > The two formulas are exactly the same, the only differences are the
> >> > cell
> >> > reference, and the sheet name. It still doesn't work.
> >> >
> >> > "HR" wrote:
> >> >
> >> > > 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?
> >>
> >> formula should look L1 in other sheets?
> >> if so try to put $L$1 instead of L1
> >> mcg
> >>
> >>
> >>
>
>
>

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


--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"HR" <[email protected]> wrote in message
news:[email protected]...
>I e-mailed the book to you. Any help would be greatly appreciated! Thanks
>in
> advance.
>
> "JulieD" wrote:
>
>> 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
>>
>>
>> "HR" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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".
>> >
>> > "Macgru" wrote:
>> >
>> >>
>> >> U¿ytkownik "HR" <[email protected]> napisa³ w wiadomo¶ci
>> >> news:[email protected]...
>> >> > The two formulas are exactly the same, the only differences are the
>> >> > cell
>> >> > reference, and the sheet name. It still doesn't work.
>> >> >
>> >> > "HR" wrote:
>> >> >
>> >> > > 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?
>> >>
>> >> formula should look L1 in other sheets?
>> >> if so try to put $L$1 instead of L1
>> >> mcg
>> >>
>> >>
>> >>
>>
>>
>>