Reference cells in Functions

D

Deb Blair

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!
 
F

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)
 
B

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)

Deb Blair said:
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
Click to expand...
want "495" to be a variable, which comes from E11 on sheet1. I cannot get
it to work.
 
B

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)
 
Top