Path to Lookup from another document

[Boethius1]

Hi, first post ever ever! Can you help?


When using worksheet "A" I want an equation to look at one cell reference in worksheet "B" searching for same reference and pull information through. Worksheet "B" may not be open when lookup needs to operate.

[Bob Phillips]

Open workbook B.
Goto workbook A.
In your cell, type = then Ctrl-F6 to get to workbook, select the cell, then
Enter.

Workbook A will update the path when workbook B is closed.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Boethius1" <[email protected]> wrote
in message news:[email protected]...
>
> Hi, first post ever ever! Can you help?
>
>
> When using worksheet "A" I want an equation to look at one cell
> reference in worksheet "B" searching for same reference and pull
> information through. Worksheet "B" may not be open when lookup needs to
> operate.
>
>
> --
> Boethius1
>
>
> ------------------------------------------------------------------------
> Boethius1's Profile:
http://www.excelforum.com/member.php...o&userid=30497
> View this thread: http://www.excelforum.com/showthread...hreadid=501503
>

[Boethius1]

[QUOTE=Bob Phillips]Open workbook B.
Goto workbook A.
In your cell, type = then Ctrl-F6 to get to workbook, select the cell, then
Enter.

Workbook A will update the path when workbook B is closed.

...................

Wow that was a quick reply, thanks it works great BUT!

with that method I am still doing the search, I want an automatic method. I have typed the following equation but it is not working, any ideas why path is wrong?

=VLOOKUPC10('C:\Documents and Settings\SharonS\My Documents\New Code Setup\[gcodenewsetupcopy.xls]Sheet1'!C4:H81,3)

With this equation I am trying to look up number from C4 within the range of C4 to H41 from the file shown in path so that it will pull through the text from column 3.

[ERR229]

The syntax is incorrect - your paran is in the wrong place and you're missing
a comma:

You have
=VLOOKUPC10('C:\Documents and Settings\SharonS\My Documents\New Code
Setup\[gcodenewsetupcopy.xls]Sheet1'!C4:H81,3)

You should have
=VLOOKUP(C10,'C:\Documents and Settings\SharonS\My Documents\New Code
Setup\gcodenewsetupcopy.xlsSheet1'!C4:H81,3)

BTW, you may want to add the final range argument to be "false" to avoid the
lookup bringing in the "closest match" to the requested data.
=VLOOKUPC10('C:\Documents and Settings\SharonS\My Documents\New Code
Setup\[gcodenewsetupcopy.xls]Sheet1'!C4:H81,3, false)

Hope this helps.
--
ERR229


"Boethius1" wrote:

>
> Bob Phillips Wrote:
> > Open workbook B.
> > Goto workbook A.
> > In your cell, type = then Ctrl-F6 to get to workbook, select the cell,
> > then
> > Enter.
> >
> > Workbook A will update the path when workbook B is closed.
> >
> > ...................
> >
> > Wow that was a quick reply, thanks it works great BUT!
> >
> > with that method I am still doing the search, I want an automatic
> > method. I have typed the following equation but it is not working, any
> > ideas why path is wrong?
> >
> > =VLOOKUPC10('C:\Documents and Settings\SharonS\My Documents\New Code
> > Setup\[gcodenewsetupcopy.xls]Sheet1'!C4:H81,3)
> >
> > With this equation I am trying to look up number from C4 within the
> > range of C4 to H41 from the file shown in path so that it will pull
> > through the text from column 3.
>
>
> --
> Boethius1
>
>
> ------------------------------------------------------------------------
> Boethius1's Profile: http://www.excelforum.com/member.php...o&userid=30497
> View this thread: http://www.excelforum.com/showthread...hreadid=501503
>
>

[Boethius1]

Hi, thanks for your reply. However still can't get to work.

Have simplified path to

=VLOOKUP(C10,'C:\New Code Set up\[gcodenewsetupcopy.xls]Sheet1'!C4:H81,3)

When ask it to look it does not pull through info and deletes the C: drive and folder path from equation to leave

=VLOOKUP(C10,[gcodenewsetupcopy.xls]Sheet1'!C4:H81,3)

Very confusing!

Thanks for the BTW but first things first!

Thanks, Sharon

----------------------

The syntax is incorrect - your paran is in the wrong place and you're missing
a comma:

You have
=VLOOKUPC10('C:\Documents and Settings\SharonS\My Documents\New Code
Setup\[gcodenewsetupcopy.xls]Sheet1'!C4:H81,3)

You should have
=VLOOKUP(C10,'C:\Documents and Settings\SharonS\My Documents\New Code
Setup\gcodenewsetupcopy.xlsSheet1'!C4:H81,3)

BTW, you may want to add the final range argument to be "false" to avoid the
lookup bringing in the "closest match" to the requested data.
=VLOOKUPC10('C:\Documents and Settings\SharonS\My Documents\New Code
Setup\[gcodenewsetupcopy.xls]Sheet1'!C4:H81,3, false)

Hope this helps.
--
ERR229


"Boethius1" wrote:

>
> Bob Phillips Wrote:
> > Open workbook B.
> > Goto workbook A.
> > In your cell, type = then Ctrl-F6 to get to workbook, select the cell,
> > then
> > Enter.
> >
> > Workbook A will update the path when workbook B is closed.
> >
> > ...................
> >
> > Wow that was a quick reply, thanks it works great BUT!
> >
> > with that method I am still doing the search, I want an automatic
> > method. I have typed the following equation but it is not working, any
> > ideas why path is wrong?
> >
> > =VLOOKUPC10('C:\Documents and Settings\SharonS\My Documents\New Code
> > Setup\[gcodenewsetupcopy.xls]Sheet1'!C4:H81,3)
> >
> > With this equation I am trying to look up number from C4 within the
> > range of C4 to H41 from the file shown in path so that it will pull
> > through the text from column 3.
>
>
> --
> Boethius1
>
>
> ------------------------------------------------------------------------
> Boethius1's Profile: http://www.excelforum.com/member.php...o&userid=30497
> View this thread: http://www.excelforum.com/showthread...hreadid=501503
>
>

[Peo Sjoblom]

That's because the workbook you lookup in is open, if you close it you'll
get the full path

--
Regards,

Peo Sjoblom

Portland, Oregon




"Boethius1" <[email protected]> wrote in
message news:[email protected]...
>
> Hi, thanks for your reply. However still can't get to work.
>
> Have simplified path to
>
> =VLOOKUP(C10,'C:\New Code Set
> up\[gcodenewsetupcopy.xls]Sheet1'!C4:H81,3)
>
> When ask it to look it does not pull through info and deletes the C:
> drive and folder path from equation to leave
>
> =VLOOKUP(C10,[gcodenewsetupcopy.xls]Sheet1'!C4:H81,3)
>
> Very confusing!
>
> Thanks for the BTW but first things first!
>
> Thanks, Sharon
>
> ----------------------
>
>
> ERR229 Wrote:
>> The syntax is incorrect - your paran is in the wrong place and you're
>> missing
>> a comma:
>>
>> You have
>> =VLOOKUPC10('C:\Documents and Settings\SharonS\My Documents\New Code
>> Setup\[gcodenewsetupcopy.xls]Sheet1'!C4:H81,3)
>>
>> You should have
>> =VLOOKUP(C10,'C:\Documents and Settings\SharonS\My Documents\New Code
>> Setup\gcodenewsetupcopy.xlsSheet1'!C4:H81,3)
>>
>> BTW, you may want to add the final range argument to be "false" to
>> avoid the
>> lookup bringing in the "closest match" to the requested data.
>> =VLOOKUPC10('C:\Documents and Settings\SharonS\My Documents\New Code
>> Setup\[gcodenewsetupcopy.xls]Sheet1'!C4:H81,3, false)
>>
>> Hope this helps.
>> --
>> ERR229
>>
>>
>> "Boethius1" wrote:
>>
>> >
>> > Bob Phillips Wrote:
>> > > Open workbook B.
>> > > Goto workbook A.
>> > > In your cell, type = then Ctrl-F6 to get to workbook, select the
>> cell,
>> > > then
>> > > Enter.
>> > >
>> > > Workbook A will update the path when workbook B is closed.
>> > >
>> > > ...................
>> > >
>> > > Wow that was a quick reply, thanks it works great BUT!
>> > >
>> > > with that method I am still doing the search, I want an automatic
>> > > method. I have typed the following equation but it is not working,
>> any
>> > > ideas why path is wrong?
>> > >
>> > > =VLOOKUPC10('C:\Documents and Settings\SharonS\My Documents\New
>> Code
>> > > Setup\[gcodenewsetupcopy.xls]Sheet1'!C4:H81,3)
>> > >
>> > > With this equation I am trying to look up number from C4 within
>> the
>> > > range of C4 to H41 from the file shown in path so that it will
>> pull
>> > > through the text from column 3.
>> >
>> >
>> > --
>> > Boethius1
>> >
>> >
>> >
>> ------------------------------------------------------------------------
>> > Boethius1's Profile:
>> http://www.excelforum.com/member.php...o&userid=30497
>> > View this thread:
>> http://www.excelforum.com/showthread...hreadid=501503
>> >
>> >
>
>
> --
> Boethius1
>
>
> ------------------------------------------------------------------------
> Boethius1's Profile:
> http://www.excelforum.com/member.php...o&userid=30497
> View this thread: http://www.excelforum.com/showthread...hreadid=501503
>

[Boethius1]

Thanks Peo, that makes sense. However problem remains the equation is still not pulling through required information.

Any ideas?

Thanks

--------------

That's because the workbook you lookup in is open, if you close it you'll
get the full path

--
Regards,

Peo Sjoblom

Portland, Oregon




"Boethius1" <[email protected]> wrote in
message news:[email protected]...
>
> Hi, thanks for your reply. However still can't get to work.
>
> Have simplified path to
>
> =VLOOKUP(C10,'C:\New Code Set
> up\[gcodenewsetupcopy.xls]Sheet1'!C4:H81,3)
>
> When ask it to look it does not pull through info and deletes the C:
> drive and folder path from equation to leave
>
> =VLOOKUP(C10,[gcodenewsetupcopy.xls]Sheet1'!C4:H81,3)
>
> Very confusing!
>
> Thanks for the BTW but first things first!
>
> Thanks, Sharon
>
> ----------------------
>
>
> ERR229 Wrote:
>> The syntax is incorrect - your paran is in the wrong place and you're
>> missing
>> a comma:
>>
>> You have
>> =VLOOKUPC10('C:\Documents and Settings\SharonS\My Documents\New Code
>> Setup\[gcodenewsetupcopy.xls]Sheet1'!C4:H81,3)
>>
>> You should have
>> =VLOOKUP(C10,'C:\Documents and Settings\SharonS\My Documents\New Code
>> Setup\gcodenewsetupcopy.xlsSheet1'!C4:H81,3)
>>
>> BTW, you may want to add the final range argument to be "false" to
>> avoid the
>> lookup bringing in the "closest match" to the requested data.
>> =VLOOKUPC10('C:\Documents and Settings\SharonS\My Documents\New Code
>> Setup\[gcodenewsetupcopy.xls]Sheet1'!C4:H81,3, false)
>>
>> Hope this helps.
>> --
>> ERR229
>>
>>
>> "Boethius1" wrote:
>>
>> >
>> > Bob Phillips Wrote:
>> > > Open workbook B.
>> > > Goto workbook A.
>> > > In your cell, type = then Ctrl-F6 to get to workbook, select the
>> cell,
>> > > then
>> > > Enter.
>> > >
>> > > Workbook A will update the path when workbook B is closed.
>> > >
>> > > ...................
>> > >
>> > > Wow that was a quick reply, thanks it works great BUT!
>> > >
>> > > with that method I am still doing the search, I want an automatic
>> > > method. I have typed the following equation but it is not working,
>> any
>> > > ideas why path is wrong?
>> > >
>> > > =VLOOKUPC10('C:\Documents and Settings\SharonS\My Documents\New
>> Code
>> > > Setup\[gcodenewsetupcopy.xls]Sheet1'!C4:H81,3)
>> > >
>> > > With this equation I am trying to look up number from C4 within
>> the
>> > > range of C4 to H41 from the file shown in path so that it will
>> pull
>> > > through the text from column 3.
>> >
>> >
>> > --
>> > Boethius1
>> >
>> >
>> >
>> ------------------------------------------------------------------------
>> > Boethius1's Profile:
>> http://www.excelforum.com/member.php...o&userid=30497
>> > View this thread:
>> http://www.excelforum.com/showthread...hreadid=501503
>> >
>> >
>
>
> --
> Boethius1
>
>
> ------------------------------------------------------------------------
> Boethius1's Profile:
> http://www.excelforum.com/member.php...o&userid=30497
> View this thread: http://www.excelforum.com/showthread...hreadid=501503
>

[Peo Sjoblom]

Can you do this test, open both workbooks, then use

=C10= go to the other work book and click on the cell that is supposedly a
match, then press enter.

If you get FALSE there might be things like trailing or leading spaces, if
imported from the web there might be invisible line feed like char 160

Also use ,0 after the 3 (column index) like

=VLOOKUP(C10,[gcodenewsetupcopy.xls]Sheet1'!C4:H81,3,0)

or FALSE like in (0 is the same as FALSE in this case)

=VLOOKUP(C10,[gcodenewsetupcopy.xls]Sheet1'!C4:H81,3,FALSE)

that way your formula looks for an exact match (I believe this was mentioned
by an earlier poster) and can explain unexpected errors

Post back if you get FALSE with the comparison cell to cell

=C10=[gcodenewsetupcopy.xls]Sheet1'!C4

replace C4 with the cell you are sure is a match in the lookup workbook





--
Regards,

Peo Sjoblom

Portland, Oregon




"Boethius1" <[email protected]> wrote
in message news:[email protected]...
>
> Thanks Peo, that makes sense. However problem remains the equation is
> still not pulling through required information.
>
> Any ideas?
>
> Thanks
>
> --------------
>
>
> Peo Sjoblom Wrote:
>> That's because the workbook you lookup in is open, if you close it
>> you'll
>> get the full path
>>
>> --
>> Regards,
>>
>> Peo Sjoblom
>>
>> Portland, Oregon
>>
>>
>>
>>
>> "Boethius1" <[email protected]>
>> wrote in
>> message news:[email protected]...
>> >
>> > Hi, thanks for your reply. However still can't get to work.
>> >
>> > Have simplified path to
>> >
>> > =VLOOKUP(C10,'C:\New Code Set
>> > up\[gcodenewsetupcopy.xls]Sheet1'!C4:H81,3)
>> >
>> > When ask it to look it does not pull through info and deletes the C:
>> > drive and folder path from equation to leave
>> >
>> > =VLOOKUP(C10,[gcodenewsetupcopy.xls]Sheet1'!C4:H81,3)
>> >
>> > Very confusing!
>> >
>> > Thanks for the BTW but first things first!
>> >
>> > Thanks, Sharon
>> >
>> > ----------------------
>> >
>> >
>> > ERR229 Wrote:
>> >> The syntax is incorrect - your paran is in the wrong place and
>> you're
>> >> missing
>> >> a comma:
>> >>
>> >> You have
>> >> =VLOOKUPC10('C:\Documents and Settings\SharonS\My Documents\New
>> Code
>> >> Setup\[gcodenewsetupcopy.xls]Sheet1'!C4:H81,3)
>> >>
>> >> You should have
>> >> =VLOOKUP(C10,'C:\Documents and Settings\SharonS\My Documents\New
>> Code
>> >> Setup\gcodenewsetupcopy.xlsSheet1'!C4:H81,3)
>> >>
>> >> BTW, you may want to add the final range argument to be "false" to
>> >> avoid the
>> >> lookup bringing in the "closest match" to the requested data.
>> >> =VLOOKUPC10('C:\Documents and Settings\SharonS\My Documents\New
>> Code
>> >> Setup\[gcodenewsetupcopy.xls]Sheet1'!C4:H81,3, false)
>> >>
>> >> Hope this helps.
>> >> --
>> >> ERR229
>> >>
>> >>
>> >> "Boethius1" wrote:
>> >>
>> >> >
>> >> > Bob Phillips Wrote:
>> >> > > Open workbook B.
>> >> > > Goto workbook A.
>> >> > > In your cell, type = then Ctrl-F6 to get to workbook, select
>> the
>> >> cell,
>> >> > > then
>> >> > > Enter.
>> >> > >
>> >> > > Workbook A will update the path when workbook B is closed.
>> >> > >
>> >> > > ...................
>> >> > >
>> >> > > Wow that was a quick reply, thanks it works great BUT!
>> >> > >
>> >> > > with that method I am still doing the search, I want an
>> automatic
>> >> > > method. I have typed the following equation but it is not
>> working,
>> >> any
>> >> > > ideas why path is wrong?
>> >> > >
>> >> > > =VLOOKUPC10('C:\Documents and Settings\SharonS\My Documents\New
>> >> Code
>> >> > > Setup\[gcodenewsetupcopy.xls]Sheet1'!C4:H81,3)
>> >> > >
>> >> > > With this equation I am trying to look up number from C4 within
>> >> the
>> >> > > range of C4 to H41 from the file shown in path so that it will
>> >> pull
>> >> > > through the text from column 3.
>> >> >
>> >> >
>> >> > --
>> >> > Boethius1
>> >> >
>> >> >
>> >> >
>> >>
>> ------------------------------------------------------------------------
>> >> > Boethius1's Profile:
>> >> http://www.excelforum.com/member.php...o&userid=30497
>> >> > View this thread:
>> >> http://www.excelforum.com/showthread...hreadid=501503
>> >> >
>> >> >
>> >
>> >
>> > --
>> > Boethius1
>> >
>> >
>> >
>> ------------------------------------------------------------------------
>> > Boethius1's Profile:
>> > http://www.excelforum.com/member.php...o&userid=30497
>> > View this thread:
>> http://www.excelforum.com/showthread...hreadid=501503
>> >
>
>
> --
> Boethius1
>
>
> ------------------------------------------------------------------------
> Boethius1's Profile:
> http://www.excelforum.com/member.php...o&userid=30497
> View this thread: http://www.excelforum.com/showthread...hreadid=501503
>

[Boethius1]

Hi and thanks, problem is sorted. Think it might have been the "FALSE" addition but I have a feeling it was more to do with me walking away from the problem for a couple of days!! Being self-trained on excel I am sure it could get it to work a lot harder for me if I knew what I was doing!
Thanks again, I am working on my next problem.

--------------------

Can you do this test, open both workbooks, then use

=C10= go to the other work book and click on the cell that is supposedly a
match, then press enter.

If you get FALSE there might be things like trailing or leading spaces, if
imported from the web there might be invisible line feed like char 160

Also use ,0 after the 3 (column index) like

=VLOOKUP(C10,[gcodenewsetupcopy.xls]Sheet1'!C4:H81,3,0)

or FALSE like in (0 is the same as FALSE in this case)

=VLOOKUP(C10,[gcodenewsetupcopy.xls]Sheet1'!C4:H81,3,FALSE)

that way your formula looks for an exact match (I believe this was mentioned
by an earlier poster) and can explain unexpected errors

Post back if you get FALSE with the comparison cell to cell

=C10=[gcodenewsetupcopy.xls]Sheet1'!C4

replace C4 with the cell you are sure is a match in the lookup workbook





--
Regards,

Peo Sjoblom

Portland, Oregon




"Boethius1" <[email protected]> wrote
in message news:[email protected]...
>
> Thanks Peo, that makes sense. However problem remains the equation is
> still not pulling through required information.
>
> Any ideas?
>
> Thanks
>
> --------------
>
>
> Peo Sjoblom Wrote:
>> That's because the workbook you lookup in is open, if you close it
>> you'll
>> get the full path
>>
>> --
>> Regards,
>>
>> Peo Sjoblom
>>
>> Portland, Oregon
>>
>>
>>
>>
>> "Boethius1" <[email protected]>
>> wrote in
>> message news:[email protected]...
>> >
>> > Hi, thanks for your reply. However still can't get to work.
>> >
>> > Have simplified path to
>> >
>> > =VLOOKUP(C10,'C:\New Code Set
>> > up\[gcodenewsetupcopy.xls]Sheet1'!C4:H81,3)
>> >
>> > When ask it to look it does not pull through info and deletes the C:
>> > drive and folder path from equation to leave
>> >
>> > =VLOOKUP(C10,[gcodenewsetupcopy.xls]Sheet1'!C4:H81,3)
>> >
>> > Very confusing!
>> >
>> > Thanks for the BTW but first things first!
>> >
>> > Thanks, Sharon
>> >
>> > ----------------------
>> >
>> >
>> > ERR229 Wrote:
>> >> The syntax is incorrect - your paran is in the wrong place and
>> you're
>> >> missing
>> >> a comma:
>> >>
>> >> You have
>> >> =VLOOKUPC10('C:\Documents and Settings\SharonS\My Documents\New
>> Code
>> >> Setup\[gcodenewsetupcopy.xls]Sheet1'!C4:H81,3)
>> >>
>> >> You should have
>> >> =VLOOKUP(C10,'C:\Documents and Settings\SharonS\My Documents\New
>> Code
>> >> Setup\gcodenewsetupcopy.xlsSheet1'!C4:H81,3)
>> >>
>> >> BTW, you may want to add the final range argument to be "false" to
>> >> avoid the
>> >> lookup bringing in the "closest match" to the requested data.
>> >> =VLOOKUPC10('C:\Documents and Settings\SharonS\My Documents\New
>> Code
>> >> Setup\[gcodenewsetupcopy.xls]Sheet1'!C4:H81,3, false)
>> >>
>> >> Hope this helps.
>> >> --
>> >> ERR229
>> >>
>> >>
>> >> "Boethius1" wrote:
>> >>
>> >> >
>> >> > Bob Phillips Wrote:
>> >> > > Open workbook B.
>> >> > > Goto workbook A.
>> >> > > In your cell, type = then Ctrl-F6 to get to workbook, select
>> the
>> >> cell,
>> >> > > then
>> >> > > Enter.
>> >> > >
>> >> > > Workbook A will update the path when workbook B is closed.
>> >> > >
>> >> > > ...................
>> >> > >
>> >> > > Wow that was a quick reply, thanks it works great BUT!
>> >> > >
>> >> > > with that method I am still doing the search, I want an
>> automatic
>> >> > > method. I have typed the following equation but it is not
>> working,
>> >> any
>> >> > > ideas why path is wrong?
>> >> > >
>> >> > > =VLOOKUPC10('C:\Documents and Settings\SharonS\My Documents\New
>> >> Code
>> >> > > Setup\[gcodenewsetupcopy.xls]Sheet1'!C4:H81,3)
>> >> > >
>> >> > > With this equation I am trying to look up number from C4 within
>> >> the
>> >> > > range of C4 to H41 from the file shown in path so that it will
>> >> pull
>> >> > > through the text from column 3.
>> >> >
>> >> >
>> >> > --
>> >> > Boethius1
>> >> >
>> >> >
>> >> >
>> >>
>> ------------------------------------------------------------------------
>> >> > Boethius1's Profile:
>> >> http://www.excelforum.com/member.php...o&userid=30497
>> >> > View this thread:
>> >> http://www.excelforum.com/showthread...hreadid=501503
>> >> >
>> >> >
>> >
>> >
>> > --
>> > Boethius1
>> >
>> >
>> >
>> ------------------------------------------------------------------------
>> > Boethius1's Profile:
>> > http://www.excelforum.com/member.php...o&userid=30497
>> > View this thread:
>> http://www.excelforum.com/showthread...hreadid=501503
>> >
>
>
> --
> Boethius1
>
>
> ------------------------------------------------------------------------
> Boethius1's Profile:
> http://www.excelforum.com/member.php...o&userid=30497
> View this thread: http://www.excelforum.com/showthread...hreadid=501503
>