Indirect call of a column from different sheet

问题:

I know how to use the indirect formula in a sheet itself.

=INDIRECT(CHAR(ROW(A5))&ROW(A5)) 

However, I am having hard time manipulating this to find a formula from different sheet called 'sheet1'

I am trying to retrieve value in B3 of Sheet1 using indirect formula. Any help is appreciated.

Please note, going forward (I plan to drag this formula down) I plan to manipulate rows and columns so I do want both of them (rows and columns) as variable values.

Eg: NOT indirect('Sheet1'!B3) but rather something like indirect('Sheet1!'&char(row(a5))...etc) which is not working for me.

Thanks for the help!

回答1:

=INDIRECT(CHAR(ROW(A5))&ROW(A5)) just returns #REF

do not use something like CHAR() to build a "A1" address. Better use R1C1:

=INDIRECT("'Sheet1'!R3C2",0)

to make the row dragable:

=INDIRECT("'Sheet1'!R"&ROW()&"C2",0)

or to fit the columns:

=INDIRECT("'Sheet1'!R3C"&COLUMN(),0)

or for both:

=INDIRECT("'Sheet1'!R"&ROW()&"C"&COLUMN(),0)