Indirect Formula

[djw1998]

Hello,

I am using the following formula and trying to drag horizontally without just copying the reference to the current cell. I am not very familiar with the indirect formula but this could help save me hours worth of manual linking..

=INDIRECT("'"&B2&"'!c37")

B2 is the tab name and C37 is the cell. I would like to drag horizontally across columns and auto-populate.

Thanks!

 

[etaf]

do you want as you drag across columns for the formula to change
from
=INDIRECT("'"&B2&"'!c37")
to
=INDIRECT("'"&B2&"'!D37")
=INDIRECT("'"&B2&"'!E37")

if so then you need to use a COLUMN() function with the Address() function

what column is
=INDIRECT("'"&B2&"'!c37")
IN
then you can use
COLUMN()+??? depending on where it starts

assuming the formula start in column B
=INDIRECT("'"&$B$2&"'!"&ADDRESS(37,COLUMN()+1))

SO as the formula is in columnB and you want columnC

,COLUMN()+1 willl return C
as you move across the column will change and with the plus 1 - D,E,F etc

Book6
	A	B	C	D	E
1
2		Sheet1
3
4		fred	John	Harry
36
37			fred	John	Harry
38
Sheet1

Cell Formulas
Range	Formula
B4:D4	B4	=INDIRECT("'"&$B$2&"'!"&ADDRESS(37,COLUMN()+1))

 

[djw1998]

THIS IS GREAT!

One question, what if my formula is in column "I" instead of column B?

 

[etaf]

then I is the 9th column
and C is the 3rd column
so you need to setup for C
it would be column() - which will return a 9 when its in I
-6 will bring it back to 3 , which is C

=INDIRECT("'"&$B$2&"'!"&ADDRESS(37,COLUMN()-6))

so as you copy across -
9-6 = 3 C
10-6 = 4 D
11-6 =5 E

Book6
	A	B	C	D	E	F	G	H	I	J	K	L
1
2		Sheet1
3
4		fred	John	Harry					fred	John	Harry
36
37			fred	John	Harry
38
Sheet1

Cell Formulas
Range	Formula
B4:D4	B4	=INDIRECT("'"&$B$2&"'!"&ADDRESS(37,COLUMN()+1))
I4:K4	I4	=INDIRECT("'"&$B$2&"'!"&ADDRESS(37,COLUMN()-6))

if you just put column() into a cell - see ROW 40
you can see what column you get
A=1 etc

hope that helps explain

you can do the same with ROW()
so row 37 can be changed using the same technique

 

[djw1998]

Thank you, this was a huge help and time saver!

 

[etaf]

you are welcome