Indirect call of a column from different sheet

1
votes

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
excelexcel-formula
Hi Scott, Thanks for the answer but how can it be manipulated? eg: if I want the column to increase by +3...would it simply be =INDIRECT("'Sheet1'!" &CHAR(ROW(B3)+3)&ROW(B3)) ? Thanks! – Dingo
That depends on what coulmn you are shooting for. To get column A you would need to add 62. B add 63 and so forth. There may be a better formula than what you are trying. If you show some data and what you are trying to accomplish a better formula may be suggested. – Scott Craner
For example to get the same, You could do ='Sheet1'!$A3 this would keep the column static while the rows change when dragged. As I read your formula it does the same thing. – Scott Craner

1 Answers

8
votes

=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)