vLookup to another open spreadsheet

J

Jill

Hello,
I need some help with this formula. I'm trying to do a
vLookup from my active sheet to another open spreadsheet.
The other spreadsheet has been named 0618mrp.xls. The
mmdd (06/18) has been defined earlier in the macro as
fDate. The macro will effectively open the file using
fDate & mrp.xls. The problem is when I use fDate &
mrp.xls in a vLookup Excel opens a window and wants me to
select a file to refer to. The file I want to refer to
is already open but Excel won't recognize it in the
vLookup. This is the formula:
ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[IDA001-Intransit.xls]Sheet1'!
R1C1:R19C2,2,FALSE)"

I would really appreciate your help,
Jill
 
V

Vasant Nanavati

I must be missing something. If the worksheet is called 0618mrp.xls, why is
the formulqa referring to IDA001-Intransit.xls?
 
J

Jill

Oops...Sorry,
The formula is
ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[fDate & mrp.xls]Sheet1'!
C1:C4,4,FALSE)"
Click to expand...
-----Original Message-----
I must be missing something. If the worksheet is called 0618mrp.xls, why is
the formulqa referring to IDA001-Intransit.xls?

--

Vasant

Hello,
I need some help with this formula. I'm trying to do a
vLookup from my active sheet to another open spreadsheet.
The other spreadsheet has been named 0618mrp.xls. The
mmdd (06/18) has been defined earlier in the macro as
fDate. The macro will effectively open the file using
fDate & mrp.xls. The problem is when I use fDate &
mrp.xls in a vLookup Excel opens a window and wants me to
select a file to refer to. The file I want to refer to
is already open but Excel won't recognize it in the
vLookup. This is the formula:
ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[IDA001-Intransit.xls]Sheet1'!
R1C1:R19C2,2,FALSE)"

I would really appreciate your help,
Jill
Click to expand...


.
Click to expand...
 
E

Earl Kiosterud

Jill,

Why is the file name IDA001-Intransit.xls in the VLOOKUP you're building
when you say you're opening 0618mrp.xls. Have I missed something? The file
doesn't have to be open anyway for the VLOOKUP to get hold of it. But if it
doesn't exist, you'll get a dialog titled "Update values" which lists files.
 
V

Vasant Nanavati

Aha. Then you need to break up the string as well as write the lookup table
reference in R1C1 format:

"=VLOOKUP(RC[-1],'[" & fDate & "mrp.xls]Sheet1'!R1C3:R4C3,4,FALSE)"

--

Vasant

Jill said:
Oops...Sorry,
The formula is
ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[fDate & mrp.xls]Sheet1'!
C1:C4,4,FALSE)"
Click to expand...
-----Original Message-----
I must be missing something. If the worksheet is called 0618mrp.xls, why is
the formulqa referring to IDA001-Intransit.xls?

--

Vasant

Hello,
I need some help with this formula. I'm trying to do a
vLookup from my active sheet to another open spreadsheet.
The other spreadsheet has been named 0618mrp.xls. The
mmdd (06/18) has been defined earlier in the macro as
fDate. The macro will effectively open the file using
fDate & mrp.xls. The problem is when I use fDate &
mrp.xls in a vLookup Excel opens a window and wants me to
select a file to refer to. The file I want to refer to
is already open but Excel won't recognize it in the
vLookup. This is the formula:
ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[IDA001-Intransit.xls]Sheet1'!
R1C1:R19C2,2,FALSE)"

I would really appreciate your help,
Jill
Click to expand...


.
Click to expand...
Click to expand...
 
E

Earl Kiosterud

Jill,

It's looking for a file named "fDate & mrp.xls". Try this (untested):

ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[" & fDate & "mrp.xls]Sheet1'!
C1:C4,4,FALSE)"

The FDate needs to be outside the strings or it's literally "fDate".
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Jill said:
Oops...Sorry,
The formula is
ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[fDate & mrp.xls]Sheet1'!
C1:C4,4,FALSE)"
Click to expand...
-----Original Message-----
I must be missing something. If the worksheet is called 0618mrp.xls, why is
the formulqa referring to IDA001-Intransit.xls?

--

Vasant

Hello,
I need some help with this formula. I'm trying to do a
vLookup from my active sheet to another open spreadsheet.
The other spreadsheet has been named 0618mrp.xls. The
mmdd (06/18) has been defined earlier in the macro as
fDate. The macro will effectively open the file using
fDate & mrp.xls. The problem is when I use fDate &
mrp.xls in a vLookup Excel opens a window and wants me to
select a file to refer to. The file I want to refer to
is already open but Excel won't recognize it in the
vLookup. This is the formula:
ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[IDA001-Intransit.xls]Sheet1'!
R1C1:R19C2,2,FALSE)"

I would really appreciate your help,
Jill
Click to expand...


.
Click to expand...
Click to expand...
 
J

Jill

Earl,
That works great. The extra " and & and " is what the
formula needed. Thank you sooooo much for your help. I
truely spent hours trying to figure this out. Thank you
again!
Jill
-----Original Message-----
Jill,

It's looking for a file named "fDate & mrp.xls". Try this (untested):

ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[" & fDate & "mrp.xls]Sheet1'!
C1:C4,4,FALSE)"

The FDate needs to be outside the strings or it's literally "fDate".
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Oops...Sorry,
The formula is
ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[fDate & mrp.xls]Sheet1'!
C1:C4,4,FALSE)"
Click to expand...
-----Original Message-----
I must be missing something. If the worksheet is
Click to expand...
called
0618mrp.xls, why is
the formulqa referring to IDA001-Intransit.xls?

--

Vasant

Hello,
I need some help with this formula. I'm trying to do a
vLookup from my active sheet to another open spreadsheet.
The other spreadsheet has been named 0618mrp.xls. The
mmdd (06/18) has been defined earlier in the macro as
fDate. The macro will effectively open the file using
fDate & mrp.xls. The problem is when I use fDate &
mrp.xls in a vLookup Excel opens a window and wants
Click to expand...
me
to
select a file to refer to. The file I want to refer to
is already open but Excel won't recognize it in the
vLookup. This is the formula:
ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[IDA001-Intransit.xls]Sheet1'!
R1C1:R19C2,2,FALSE)"

I would really appreciate your help,
Jill


.
Click to expand...
Click to expand...


.
Click to expand...
 
V

Vasant Nanavati

Hey! What about me? I posted a minute before Earl!

Seriously, though, I think Earl's solution (based on your original attempt)
may give you the wrong references. C1:C4 will be interpreted as A:D since
you are using R1C1 notation.

--

Vasant

Jill said:
Earl,
That works great. The extra " and & and " is what the
formula needed. Thank you sooooo much for your help. I
truely spent hours trying to figure this out. Thank you
again!
Jill
-----Original Message-----
Jill,

It's looking for a file named "fDate & mrp.xls". Try this (untested):

ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[" & fDate & "mrp.xls]Sheet1'!
C1:C4,4,FALSE)"

The FDate needs to be outside the strings or it's literally "fDate".
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Oops...Sorry,
The formula is
ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[fDate & mrp.xls]Sheet1'!
C1:C4,4,FALSE)"

-----Original Message-----
I must be missing something. If the worksheet is called
0618mrp.xls, why is
the formulqa referring to IDA001-Intransit.xls?

--

Vasant

message
Hello,
I need some help with this formula. I'm trying to do a
vLookup from my active sheet to another open
spreadsheet.
The other spreadsheet has been named 0618mrp.xls. The
mmdd (06/18) has been defined earlier in the macro as
fDate. The macro will effectively open the file using
fDate & mrp.xls. The problem is when I use fDate &
mrp.xls in a vLookup Excel opens a window and wants me
to
select a file to refer to. The file I want to refer to
is already open but Excel won't recognize it in the
vLookup. This is the formula:
ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[IDA001-Intransit.xls]Sheet1'!
R1C1:R19C2,2,FALSE)"

I would really appreciate your help,
Jill


.
Click to expand...


.
Click to expand...
Click to expand...
 
E

Earl Kiosterud

Vasant,

Her first formula actually was all in RC notation. I didn't notice it'd
changed along the way, and was only getting variable FDate out of the
string. As you did a minute earlier. :) Do you suppose the newsgroup
servers actually conspire to hold off on posts so we'll simultaneously work
on similar answers? :)
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Vasant Nanavati said:
Hey! What about me? I posted a minute before Earl!

Seriously, though, I think Earl's solution (based on your original attempt)
may give you the wrong references. C1:C4 will be interpreted as A:D since
you are using R1C1 notation.

--

Vasant

Jill said:
Earl,
That works great. The extra " and & and " is what the
formula needed. Thank you sooooo much for your help. I
truely spent hours trying to figure this out. Thank you
again!
Jill
-----Original Message-----
Jill,

It's looking for a file named "fDate & mrp.xls". Try this (untested):

ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[" & fDate & "mrp.xls]Sheet1'!
C1:C4,4,FALSE)"

The FDate needs to be outside the strings or it's literally "fDate".
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Oops...Sorry,
The formula is
ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[fDate & mrp.xls]Sheet1'!
C1:C4,4,FALSE)"

-----Original Message-----
I must be missing something. If the worksheet is called
0618mrp.xls, why is
the formulqa referring to IDA001-Intransit.xls?

--

Vasant

message
Hello,
I need some help with this formula. I'm trying to do a
vLookup from my active sheet to another open
spreadsheet.
The other spreadsheet has been named 0618mrp.xls. The
mmdd (06/18) has been defined earlier in the macro as
fDate. The macro will effectively open the file using
fDate & mrp.xls. The problem is when I use fDate &
mrp.xls in a vLookup Excel opens a window and wants me
to
select a file to refer to. The file I want to refer to
is already open but Excel won't recognize it in the
vLookup. This is the formula:
ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[IDA001-Intransit.xls]Sheet1'!
R1C1:R19C2,2,FALSE)"

I would really appreciate your help,
Jill


.



.
Click to expand...
Click to expand...
Click to expand...
 
V

Vasant Nanavati

Good catch, Earl ... I didn't notice the change along the way in the OP's
"original" formula either <g>.

Regards,

Vasant.

Earl Kiosterud said:
Vasant,

Her first formula actually was all in RC notation. I didn't notice it'd
changed along the way, and was only getting variable FDate out of the
string. As you did a minute earlier. :) Do you suppose the newsgroup
servers actually conspire to hold off on posts so we'll simultaneously work
on similar answers? :)
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Vasant Nanavati said:
Hey! What about me? I posted a minute before Earl!

Seriously, though, I think Earl's solution (based on your original attempt)
may give you the wrong references. C1:C4 will be interpreted as A:D since
you are using R1C1 notation.

--

Vasant

Jill said:
Earl,
That works great. The extra " and & and " is what the
formula needed. Thank you sooooo much for your help. I
truely spent hours trying to figure this out. Thank you
again!
Jill
-----Original Message-----
Jill,

It's looking for a file named "fDate & mrp.xls". Try
this (untested):

ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[" & fDate & "mrp.xls]Sheet1'!
C1:C4,4,FALSE)"

The FDate needs to be outside the strings or it's
literally "fDate".
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

message
Oops...Sorry,
The formula is
ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[fDate & mrp.xls]Sheet1'!
C1:C4,4,FALSE)"

-----Original Message-----
I must be missing something. If the worksheet is
called
0618mrp.xls, why is
the formulqa referring to IDA001-Intransit.xls?

--

Vasant

message
Hello,
I need some help with this formula. I'm trying to
do a
vLookup from my active sheet to another open
spreadsheet.
The other spreadsheet has been named 0618mrp.xls.
The
mmdd (06/18) has been defined earlier in the macro
as
fDate. The macro will effectively open the file
using
fDate & mrp.xls. The problem is when I use fDate &
mrp.xls in a vLookup Excel opens a window and wants
me
to
select a file to refer to. The file I want to
refer to
is already open but Excel won't recognize it in the
vLookup. This is the formula:
ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[IDA001-Intransit.xls]Sheet1'!
R1C1:R19C2,2,FALSE)"

I would really appreciate your help,
Jill


.



.
Click to expand...
Click to expand...
Click to expand...
 
T

Tom Ogilvy

Hi Vasant,

maybe she does want A:D.

note that in your post you do
"=VLOOKUP(RC[-1],'[" & fDate & "mrp.xls]Sheet1'!R1C3:R4C3,4,FALSE)"

rows 1 to 4 of column 3, a single column, then ask for the results from
column 4 of the range - a definite error situation <g>

--
Regards,
Tom Ogilvy



Vasant Nanavati said:
Hey! What about me? I posted a minute before Earl!

Seriously, though, I think Earl's solution (based on your original attempt)
may give you the wrong references. C1:C4 will be interpreted as A:D since
you are using R1C1 notation.

--

Vasant

Jill said:
Earl,
That works great. The extra " and & and " is what the
formula needed. Thank you sooooo much for your help. I
truely spent hours trying to figure this out. Thank you
again!
Jill
-----Original Message-----
Jill,

It's looking for a file named "fDate & mrp.xls". Try this (untested):

ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[" & fDate & "mrp.xls]Sheet1'!
C1:C4,4,FALSE)"

The FDate needs to be outside the strings or it's literally "fDate".
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Oops...Sorry,
The formula is
ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[fDate & mrp.xls]Sheet1'!
C1:C4,4,FALSE)"

-----Original Message-----
I must be missing something. If the worksheet is called
0618mrp.xls, why is
the formulqa referring to IDA001-Intransit.xls?

--

Vasant

message
Hello,
I need some help with this formula. I'm trying to do a
vLookup from my active sheet to another open
spreadsheet.
The other spreadsheet has been named 0618mrp.xls. The
mmdd (06/18) has been defined earlier in the macro as
fDate. The macro will effectively open the file using
fDate & mrp.xls. The problem is when I use fDate &
mrp.xls in a vLookup Excel opens a window and wants me
to
select a file to refer to. The file I want to refer to
is already open but Excel won't recognize it in the
vLookup. This is the formula:
ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[IDA001-Intransit.xls]Sheet1'!
R1C1:R19C2,2,FALSE)"

I would really appreciate your help,
Jill


.



.
Click to expand...
Click to expand...
Click to expand...
 
V

Vasant Nanavati

Thanks, Tom ... as usual, you are correct. I'll quit while I'm ahead in this
thread.

I really need to get more sleep ... :)

Regards,

Vasant


Tom Ogilvy said:
Hi Vasant,

maybe she does want A:D.

note that in your post you do
"=VLOOKUP(RC[-1],'[" & fDate & "mrp.xls]Sheet1'!R1C3:R4C3,4,FALSE)"

rows 1 to 4 of column 3, a single column, then ask for the results from
column 4 of the range - a definite error situation <g>

--
Regards,
Tom Ogilvy



Vasant Nanavati said:
Hey! What about me? I posted a minute before Earl!

Seriously, though, I think Earl's solution (based on your original attempt)
may give you the wrong references. C1:C4 will be interpreted as A:D since
you are using R1C1 notation.

--

Vasant

Jill said:
Earl,
That works great. The extra " and & and " is what the
formula needed. Thank you sooooo much for your help. I
truely spent hours trying to figure this out. Thank you
again!
Jill
-----Original Message-----
Jill,

It's looking for a file named "fDate & mrp.xls". Try
this (untested):

ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[" & fDate & "mrp.xls]Sheet1'!
C1:C4,4,FALSE)"

The FDate needs to be outside the strings or it's
literally "fDate".
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

message
Oops...Sorry,
The formula is
ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[fDate & mrp.xls]Sheet1'!
C1:C4,4,FALSE)"

-----Original Message-----
I must be missing something. If the worksheet is
called
0618mrp.xls, why is
the formulqa referring to IDA001-Intransit.xls?

--

Vasant

message
Hello,
I need some help with this formula. I'm trying to
do a
vLookup from my active sheet to another open
spreadsheet.
The other spreadsheet has been named 0618mrp.xls.
The
mmdd (06/18) has been defined earlier in the macro
as
fDate. The macro will effectively open the file
using
fDate & mrp.xls. The problem is when I use fDate &
mrp.xls in a vLookup Excel opens a window and wants
me
to
select a file to refer to. The file I want to
refer to
is already open but Excel won't recognize it in the
vLookup. This is the formula:
ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[IDA001-Intransit.xls]Sheet1'!
R1C1:R19C2,2,FALSE)"

I would really appreciate your help,
Jill


.



.
Click to expand...
Click to expand...
Click to expand...
 
M

MSP77079

I have found that poking functions into cells as suggested causes a lo
of problems. If nothing else, you end up with a workbook full o
functions, so that everytime you make a change it can take severa
minutes to update (unless you turn off automatic recalculation).

It is easier to program, and easier to understand later, if you use th
Application.WorkbookFunction command.

In this case you would use:
Set LookupTable = Workbooks(...).Range(...)
ResultColumn = j
LookupValue = .....
FoundValue = Application.WorkbookFunction. _
vLookUp(SearchRange, LookUpValue, j, False
 
Top