Reference cells in Functions

[Guest]

Here is my function

=INDEX('Downloaded Data'!$A$1:$H$495,MATCH(I1,'Downloaded Data'!$I$1:$I$495,0),3

This function is put into the Downloaded Data sheet via a VB procedure. I want "495" to be a variable, which comes from E11 on sheet1. I cannot get it to work.

Please help
Thank you!

 

[Frank Kabel]

Hi
try
=INDEX(INDIRECT("'Downloaded Data'!$A$1:$H$" &
'sheet1'!E11),MATCH(I1,INDIRECT("'Downloaded Data'!$I$1:$I$" &
'sheet1'!E11),0),3)

 

[Bob Phillips]

Hi Deb,

=INDEX(INDIRECT("'Downloaded Data'!$A$1:$H$" &
Sheet1!E11),MATCH(I1,INDIRECT("'Downloaded Data'!$I$1:$I$"&Sheet1!E11),0),3)


--

HTH

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

Here is my function:

=INDEX('Downloaded Data'!$A$1:$H$495,MATCH(I1,'Downloaded Data'!$I$1:$I$495,0),3)

This function is put into the Downloaded Data sheet via a VB procedure. I

want "495" to be a variable, which comes from E11 on sheet1. I cannot get
it to work.

 

[Bob Phillips]

Well Deb,

seeing as I gave exactly the same answer (bar single quotes round Sheet1) as
Frank's phenomenal answer, I am perplexed by your response.

--

HTH

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