Reference to cell in another sheet

[Nir Robinson]

Hi all,

What is the best way to make reference to cell another sheet?

I try to use the function INDIRECT and even succeed but it look bad:
=INDIRECT("'" & $C20 & "'!" &AE$3&AE$4)
Where:
c20--> name of sheet
AE3--> name of row
AE4--> name of column

Is there better way to implement that?

Thanks in advanced.
Nir

 

[Luke M]

If you really need user to be able to change all of those settings, then INDIRECT is one of the easier ways to go.
That said, does the user really need, or should they have, the responsibility of picking any row and any column from any sheet? If instead there is a particuular column/row heading you are looking for, we might be able to make do with a combination of CHOOSE, INDEX, MATCH.

 

[Nir Robinson]

Thanks for your replay Luke,

Another connected question,
How can I use variable (cell) inside index-natch function
I have this function:

=IF(ISERROR(INDEX('1'!$C:$AC,MATCH($B7,'1'!$C:$C,0),AE$2))=TRUE,0,INDEX('1'!$C:$AC,MATCH($B7,'1'!$C:$C,0),AE$2))

and I need to replace the " '1' " (name of sheet) to cell (e.g. A1)

10x

 

[Luke M]

Since the sheet names are numbers, you can easily put that into a CHOOSE function.
=IFERROR(INDEX(CHOOSE(A1,'1'!$C:$AC,'2'!$C:$AC),MATCH($B7,CHOOSE(A1,'1'!$C:$C,'2'!$C:$C),0),AE$2),0)

FYI, if the sheet names were not numbers, would need to do something like:
CHOOSE(MATCH(A1,{"Sheet1","Sheet2","Sheet3"},0),Sheet1A:C,Sheet2A:C)