Use a lookup with a variable for the workbook name
 

I have a workbook and I have to use INDEX(...,MATCH(...)) into another
workbook that I have opened and it does not accept the code:
workbook is "2006 MasterMaterials.xls" with worksheet "RawMaterial"

cString = "2006 MasterMaterials.xls"

FormulaR1C1 = "=INDEX([cString]RawMaterial!$J:$J,
MATCH(C3,[cString]RawMaterial!$C:$C,0))"

Anyone have ideas???? I have tried a number of things.

Thanks


--
Wilbur
------------------------------------------------------------------------
Wilbur's Profile: http://www.excelforum.com/member.php...o&userid=31826
View this thread: http://www.excelforum.com/showthread...hreadid=515498

Use a lookup with a variable for the workbook name
 

This won't work, but it gets you closer:

..FormulaR1C1 = "=INDEX([" & cString & "]RawMaterial!$J:$J," & _
"MATCH(C3,[" & cString & "]RawMaterial!$C:$C,0))"

But you've got another problem. .FormulaR1C1 wants the formula in R1C1
reference style.

Or maybe you want:

..Formula = "=INDEX([" & cString & "]RawMaterial!$J:$J," & _
"MATCH(C3,[" & cString & "]RawMaterial!$C:$C,0))"


Wilbur wrote:

I have a workbook and I have to use INDEX(...,MATCH(...)) into another
workbook that I have opened and it does not accept the code:
workbook is "2006 MasterMaterials.xls" with worksheet "RawMaterial"

cString = "2006 MasterMaterials.xls"

FormulaR1C1 = "=INDEX([cString]RawMaterial!$J:$J,
MATCH(C3,[cString]RawMaterial!$C:$C,0))"

Anyone have ideas???? I have tried a number of things.

Thanks

--
Wilbur
------------------------------------------------------------------------
Wilbur's Profile: http://www.excelforum.com/member.php...o&userid=31826
View this thread: http://www.excelforum.com/showthread...hreadid=515498

--

Dave Peterson

Use a lookup with a variable for the workbook name
 

Wilbur,

Take the variable cString out of the quotes. Try:

FormulaR1C1 = "=INDEX([" & cString & "]RawMaterial!$J:$J,MATCH(C3,[" &
cString & "]RawMaterial!$C:$C,0))"

HTH

Tim

"Wilbur" wrote in
message ...

I have a workbook and I have to use INDEX(...,MATCH(...)) into another
workbook that I have opened and it does not accept the code:
workbook is "2006 MasterMaterials.xls" with worksheet "RawMaterial"

cString = "2006 MasterMaterials.xls"

FormulaR1C1 = "=INDEX([cString]RawMaterial!$J:$J,
MATCH(C3,[cString]RawMaterial!$C:$C,0))"

Anyone have ideas???? I have tried a number of things.

Thanks


--
Wilbur
------------------------------------------------------------------------
Wilbur's Profile:
http://www.excelforum.com/member.php...o&userid=31826
View this thread: http://www.excelforum.com/showthread...hreadid=515498

Use a lookup with a variable for the workbook name
 

Oh! and it should start with something like:

Range("A4").Formula =

and not 'FormulaR1C1' when you use absolute cell references.

Tim

"Tim Barlow" wrote in message
...
Wilbur,

Take the variable cString out of the quotes. Try:

FormulaR1C1 = "=INDEX([" & cString & "]RawMaterial!$J:$J,MATCH(C3,[" &
cString & "]RawMaterial!$C:$C,0))"

HTH

Tim

"Wilbur" wrote in
message ...

I have a workbook and I have to use INDEX(...,MATCH(...)) into another
workbook that I have opened and it does not accept the code:
workbook is "2006 MasterMaterials.xls" with worksheet "RawMaterial"

cString = "2006 MasterMaterials.xls"

FormulaR1C1 = "=INDEX([cString]RawMaterial!$J:$J,
MATCH(C3,[cString]RawMaterial!$C:$C,0))"

Anyone have ideas???? I have tried a number of things.

Thanks


--
Wilbur
------------------------------------------------------------------------
Wilbur's Profile:
http://www.excelforum.com/member.php...o&userid=31826
View this thread:
http://www.excelforum.com/showthread...hreadid=515498