No Takers- Vlookup

have one workbook that is census and marketing data with the zipcode as the
key, it contains the named range zips1in file name zipcodes2006

what i want to do is have a currently open workbook file name midwest
region, use this zipcode workbook, keying off the zipcodes using the vlookup.

how do i write the formula to refer to another workbook, with a named range?
e.g.
vlookup(zips2, {go zipcodes.xls then zips1},field) where zips1 and zips2 are
named ranges.

i think yo can still go the c:\documents and settings\owner\excel\marketing
data\\zipcodes2006!$zips!$--can you just point and click??
Thanks

http://www.wvu.edu/~planning/XLTrain...andout%203.doc

You are correct about being able to point and click the cells on the other worksheet... but using vlookup requires a pretty thorough explanation. The link above provides a very good resource.

This might set you in the general direction:

=VLOOKUP(A1,Book1!ZipCodes,2)

Where A1 is in the workbook you want the lookup value placed in,
Book1!ZipCodes is the name of the file and the named range in that file of
the lookup table, and 2 is the column in the lookup table that has the return
value

--
Kevin Backmann


"reno" wrote:

> have one workbook that is census and marketing data with the zipcode as the
> key, it contains the named range zips1in file name zipcodes2006
>
> what i want to do is have a currently open workbook file name midwest
> region, use this zipcode workbook, keying off the zipcodes using the vlookup.
>
> how do i write the formula to refer to another workbook, with a named range?
> e.g.
> vlookup(zips2, {go zipcodes.xls then zips1},field) where zips1 and zips2 are
> named ranges.
>
> i think yo can still go the c:\documents and settings\owner\excel\marketing
> data\\zipcodes2006!$zips!$--can you just point and click??
> Thanks

wouldn't this assume that the two files are in the same directory? if not,
don't you have to specify the drive and path?

"Kevin B" wrote:

> This might set you in the general direction:
>
> =VLOOKUP(A1,Book1!ZipCodes,2)
>
> Where A1 is in the workbook you want the lookup value placed in,
> Book1!ZipCodes is the name of the file and the named range in that file of
> the lookup table, and 2 is the column in the lookup table that has the return
> value
>
> --
> Kevin Backmann
>
>
> "reno" wrote:
>
> > have one workbook that is census and marketing data with the zipcode as the
> > key, it contains the named range zips1in file name zipcodes2006
> >
> > what i want to do is have a currently open workbook file name midwest
> > region, use this zipcode workbook, keying off the zipcodes using the vlookup.
> >
> > how do i write the formula to refer to another workbook, with a named range?
> > e.g.
> > vlookup(zips2, {go zipcodes.xls then zips1},field) where zips1 and zips2 are
> > named ranges.
> >
> > i think yo can still go the c:\documents and settings\owner\excel\marketing
> > data\\zipcodes2006!$zips!$--can you just point and click??
> > Thanks

You mentioned in your first post that the file with the lookup table was
open, therefore, if you type "=Vlookup(A1," and then press CTRL+F6 to go to
the workbook with the lookup table, click INSERT in the menu, select
NAME/PASTE and paste in the range name, and then type a comma and the return
value column and press ENTER, the path name is not shown in the cell.

But, if you click EDIT in the menu while you're in the workbook that has the
VLOOKUP formula, click on LINKS and you'll see that Excel has picked up the
full path of the lookup table workbook.

Sorry for the run-on sentence it's been a long day...
--
Kevin Backmann


"reno" wrote:

> wouldn't this assume that the two files are in the same directory? if not,
> don't you have to specify the drive and path?
>
> "Kevin B" wrote:
>
> > This might set you in the general direction:
> >
> > =VLOOKUP(A1,Book1!ZipCodes,2)
> >
> > Where A1 is in the workbook you want the lookup value placed in,
> > Book1!ZipCodes is the name of the file and the named range in that file of
> > the lookup table, and 2 is the column in the lookup table that has the return
> > value
> >
> > --
> > Kevin Backmann
> >
> >
> > "reno" wrote:
> >
> > > have one workbook that is census and marketing data with the zipcode as the
> > > key, it contains the named range zips1in file name zipcodes2006
> > >
> > > what i want to do is have a currently open workbook file name midwest
> > > region, use this zipcode workbook, keying off the zipcodes using the vlookup.
> > >
> > > how do i write the formula to refer to another workbook, with a named range?
> > > e.g.
> > > vlookup(zips2, {go zipcodes.xls then zips1},field) where zips1 and zips2 are
> > > named ranges.
> > >
> > > i think yo can still go the c:\documents and settings\owner\excel\marketing
> > > data\\zipcodes2006!$zips!$--can you just point and click??
> > > Thanks

on the INsert, name and paste are not highlighted, so they are not available?

"Kevin B" wrote:

> You mentioned in your first post that the file with the lookup table was
> open, therefore, if you type "=Vlookup(A1," and then press CTRL+F6 to go to
> the workbook with the lookup table, click INSERT in the menu, select
> NAME/PASTE and paste in the range name, and then type a comma and the return
> value column and press ENTER, the path name is not shown in the cell.
>
> But, if you click EDIT in the menu while you're in the workbook that has the
> VLOOKUP formula, click on LINKS and you'll see that Excel has picked up the
> full path of the lookup table workbook.
>
> Sorry for the run-on sentence it's been a long day...
> --
> Kevin Backmann
>
>
> "reno" wrote:
>
> > wouldn't this assume that the two files are in the same directory? if not,
> > don't you have to specify the drive and path?
> >
> > "Kevin B" wrote:
> >
> > > This might set you in the general direction:
> > >
> > > =VLOOKUP(A1,Book1!ZipCodes,2)
> > >
> > > Where A1 is in the workbook you want the lookup value placed in,
> > > Book1!ZipCodes is the name of the file and the named range in that file of
> > > the lookup table, and 2 is the column in the lookup table that has the return
> > > value
> > >
> > > --
> > > Kevin Backmann
> > >
> > >
> > > "reno" wrote:
> > >
> > > > have one workbook that is census and marketing data with the zipcode as the
> > > > key, it contains the named range zips1in file name zipcodes2006
> > > >
> > > > what i want to do is have a currently open workbook file name midwest
> > > > region, use this zipcode workbook, keying off the zipcodes using the vlookup.
> > > >
> > > > how do i write the formula to refer to another workbook, with a named range?
> > > > e.g.
> > > > vlookup(zips2, {go zipcodes.xls then zips1},field) where zips1 and zips2 are
> > > > named ranges.
> > > >
> > > > i think yo can still go the c:\documents and settings\owner\excel\marketing
> > > > data\\zipcodes2006!$zips!$--can you just point and click??
> > > > Thanks

If both workbooks are open when the formula is keyed, Excel puts the full
path in for me when I close the source workbook so

=VLOOKUP(A1,Book1.xls!ZipCodes,2)

is changed automatically to

=VLOOKUP(A1,'I:\Excel\Book1.xls'!ZipCodes,2)

after typing =VLOOKUP(A1, use your mouse to navigate to the other
workbook, select/highlight your table-excel will put the proper range
reference into your fomula, then type ,2) to finish the formula and hit
Enter. Then close the source workbook. This way, Excel handles the single
quotes, brackets, exclamation points, path, etc. And, if the range is named,
excel converts the range reference to use the name instead.

BTW - I am assuming your zipcodes are sorted ascending. Your original post
omits the fourth argument for VLOOKUP which specifies an approximate match so
excel will find the largest value that is smaller than your criteria.





"reno" wrote:

> on the INsert, name and paste are not highlighted, so they are not available?
>
> "Kevin B" wrote:
>
> > You mentioned in your first post that the file with the lookup table was
> > open, therefore, if you type "=Vlookup(A1," and then press CTRL+F6 to go to
> > the workbook with the lookup table, click INSERT in the menu, select
> > NAME/PASTE and paste in the range name, and then type a comma and the return
> > value column and press ENTER, the path name is not shown in the cell.
> >
> > But, if you click EDIT in the menu while you're in the workbook that has the
> > VLOOKUP formula, click on LINKS and you'll see that Excel has picked up the
> > full path of the lookup table workbook.
> >
> > Sorry for the run-on sentence it's been a long day...
> > --
> > Kevin Backmann
> >
> >
> > "reno" wrote:
> >
> > > wouldn't this assume that the two files are in the same directory? if not,
> > > don't you have to specify the drive and path?
> > >
> > > "Kevin B" wrote:
> > >
> > > > This might set you in the general direction:
> > > >
> > > > =VLOOKUP(A1,Book1!ZipCodes,2)
> > > >
> > > > Where A1 is in the workbook you want the lookup value placed in,
> > > > Book1!ZipCodes is the name of the file and the named range in that file of
> > > > the lookup table, and 2 is the column in the lookup table that has the return
> > > > value
> > > >
> > > > --
> > > > Kevin Backmann
> > > >
> > > >
> > > > "reno" wrote:
> > > >
> > > > > have one workbook that is census and marketing data with the zipcode as the
> > > > > key, it contains the named range zips1in file name zipcodes2006
> > > > >
> > > > > what i want to do is have a currently open workbook file name midwest
> > > > > region, use this zipcode workbook, keying off the zipcodes using the vlookup.
> > > > >
> > > > > how do i write the formula to refer to another workbook, with a named range?
> > > > > e.g.
> > > > > vlookup(zips2, {go zipcodes.xls then zips1},field) where zips1 and zips2 are
> > > > > named ranges.
> > > > >
> > > > > i think yo can still go the c:\documents and settings\owner\excel\marketing
> > > > > data\\zipcodes2006!$zips!$--can you just point and click??
> > > > > Thanks