Deb,

Try this

Worksheets("Reformatted Data").Range("D1").Formula = _
"=INDEX(INDIRECT(""'Downloaded Data'!$A$1:$H$&'Start
Here'!$E$11"")," _
"MATCH(I3,INDIRECT(""'Downloaded Data'!$I$1:$I$&'Start
Here'!$E$11"),0),4)"


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Deb" wrote in message
...
The function works in Excel. But, when VB encounters the first quote, it
thinks the rest is a comment. I renamed the spreadsheets to have a
one-level name so that I could remove the single quotes, but it still didn't
work. Help!!

Worksheets("Reformatted Data").Range("D1").Formula =
"=INDEX(INDIRECT("'Downloaded Data'!$A$1:$H$"&'Start
Here'!$E$11),MATCH(I3,INDIRECT("'Downloaded Data'!$I$1:$I$"&'Start
Here'!$E$11),0),4)"

Sorry, I meant

Worksheets("Sheet1").Range("D1").Formula = _
"=INDEX(INDIRECT(""'Downloaded Data'!$A$1:$H$&'Start
Here'!$E$11"")," & _
"MATCH(I3,INDIRECT(""'Downloaded Data'!$I$1:$I$&'Start
Here'!$E$11""),0),4)"


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bob Phillips" wrote in message
...
Deb,

Try this

Worksheets("Reformatted Data").Range("D1").Formula = _
"=INDEX(INDIRECT(""'Downloaded Data'!$A$1:$H$&'Start
Here'!$E$11"")," _
"MATCH(I3,INDIRECT(""'Downloaded Data'!$I$1:$I$&'Start
Here'!$E$11"),0),4)"


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Deb" wrote in message
...
The function works in Excel. But, when VB encounters the first quote,
it
thinks the rest is a comment. I renamed the spreadsheets to have a
one-level name so that I could remove the single quotes, but it still
didn't
work. Help!!

Worksheets("Reformatted Data").Range("D1").Formula =
"=INDEX(INDIRECT("'Downloaded Data'!$A$1:$H$"&'Start
Here'!$E$11),MATCH(I3,INDIRECT("'Downloaded Data'!$I$1:$I$"&'Start
Here'!$E$11),0),4)"

Bob - This worked!! Thank you very much!! Wahoo!