Handling variables with data from another sheet on google drive calc

Question

I have a Google Drive Calc File,

Google says in their web help how to reference data from another sheet, but what happens if what I need is to make the reference depending on my fields on the new sheet?

Ok, let me explain further... Let's say I have a sheet named 'My Data' and a Sheet named 'My List'. And on 'My List' I have a column where I'll place a number (let's say column A)

• On Column A value: 15
• On Column B value: 'My Data'

On column C of the same sheet instead of

='My Data'!B15

I want to have something like

='My Data'!B@ColumnA

Where @ColumnA is the number on column A. If it's possible, I have interest of doing this:

=@ColumnB!B@ColumnA

Is it possible?

Answer 1

You use INDIRECT().

=INDIRECT(B1 & "!" & A1)

I'm assuming that the values are in row 1 and that the value in Row B is surrounded by single quotes. Otherwise, you'll have to put them in.

=INDIRECT("'" & B1 & "'!" & A1)

The & concatenates the values. You could use the function CONCATENATE() instead.

[I put spaces for readability, but they're not needed.]