problem using the INDIRECT function

[Guest]

I would like to use the INDIRECT function to look at a cell on a different
sheet to my formula. I can get this to work fine if I enter the following
formula;

=INDIRECT("'Reference Sheet'!D4") (as my second sheet is called Reference
Sheet)

However, to get the 'D4' part of the formula I am using the following formula;

=("D"&(ROW(D3)+(MATCH(C13,'Reference Sheet'!D3:$D$55,0))))

So I put the formulae together to get;

=INDIRECT("'Reference Sheet'!"D"&(ROW(D3)+(MATCH(C13,'Reference
Sheet'!D3:$D$55,0)))")

But this gives an error and highlights "D", so I removed the "" and then the
#REF! error appeared.

I have tried using brackets and inverted commas in different places but i
can't seem to get the formula to work.

Any suggestions would be appreciated.

 

[Bob Phillips]

=INDIRECT("'Reference Sheet'!D"&(ROW(D3)+(MATCH(C13,'Reference
Sheet'!D3:$D$55,0))))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Excellent, Thanks very much Bob.

=INDIRECT("'Reference Sheet'!D"&(ROW(D3)+(MATCH(C13,'Reference
Sheet'!D3:$D$55,0))))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)