Using Indirect function with a different Sheet

[Dave Lucas]

I have tried to use the indirect function as follows:
=+INDIRECT('SSI Mapping'!L3) but it returns #Ref is there something specially I need to do to make this work?

Thanks
Dave

 

[Juan Pablo González]

You can just use directly

='SSI Mapping'!L3

you use INDIRECT when you're creating a cell reference "manually". For example, if you would have in cell A1 the text

B1

and in cell B1 the value 10.

Then the formula

=INDRECT(A1)

would return 10, because its the same as doing

=INDIRECT("B1")

see ?

 

[Dave Lucas]

Yes unfortunately I am using cell L3 to construct the actual location by doing a match through a listing then concatenating "d" onto the front.
so really L3 refers to D1095 on sheet 'SSI Mapping'

Can you still do this?

Dave

 

[just_jon]

Do you have a sheetname exactly SSI Mapping -- no extra spacing? What is the value of SSI Mapping!L3?

 

[fairwinds]

If you have e.g. D5 in SSI Mapping L3 it will refere back to the sheet where you have the formula, not in SSI Mapping sheet.

 

[Aladin Akyurek]

Yes unfortunately I am using cell L3 to construct the actual location by doing a match through a listing then concatenating "d" onto the front.
so really L3 refers to D1095 on sheet 'SSI Mapping'

Can you still do this?

Dave

Would you post the formula that you have in L3?

 

[Dave Lucas]

Ok, sorry I got the solution
the formula that I have to use in L3 is
"'SSI Mapping'!"&"D"&MATCH(G3,'SSI Mapping'!C1:'SSI Mapping'!C1647,0)
I had to concatenate the spreadsheet name onto the front of the address then use the indirect function Indirect (L3).

Thanks for your help all
Dave

 

[Aladin Akyurek]

Ok, sorry I got the solution
the formula that I have to use in L3 is
"'SSI Mapping'!"&"D"&MATCH(G3,'SSI Mapping'!C1:'SSI Mapping'!C1647,0)
I had to concatenate the spreadsheet name onto the front of the address then use the indirect function Indirect (L3).

Thanks for your help all
Dave

=INDEX('SSI Mapping'!$D$1:$D$1647,MATCH(G3,'SSI Mapping'!$C$1:$C$1647,0))

should give you directly what you need.