Why indirect function

[lezawang]

Hi
I am reading/watched videos about indirect() function. I still need a simple example where it is clearly I need to use indirect() function or better to use it.

What I have read/watched are the following which tell you how to use indirect function but not clear Why?

https://www.excel-easy.com/examples/indirect.html
https://www.youtube.com/watch?v=zABwhLIuKTs

 

[RasGhul]

Indirect is a great function for calculating multiple sheets with same data sets. This saves you writing a separate formula for each sheet in one table.

 

[lezawang]

sorry what do you mean by: "calculating multiple sheets with same data sets"

 

[sheetspread]

Simple example:

Excel 2010
	A	B	C
1
2
3
4
5			38
Blue

Excel 2010
	A	B	C
1
2
3
4
5			44
Green

Excel 2010
	A	B	C
1
2
3
4
5			57
Silver

Excel 2010
	A	B	C
1
2
3
4
5			63
North

Excel 2010
	A	B
1	Blue	38
2	Green	44
3	Silver	57
4	North	63
Sheet2

Cell Formulas
Range	Formula
B1	=INDIRECT(A1&"!"&"C5")

 

[sheetspread]

Another advantage of indirect is that hard coded references never change, even if you delete rows or sort:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	2220
2							23	73	88	52	10	12	71	84
3							11	34	80	1	90	88	72	82
4							96	11	68	90	83	12	80	92
5							5	41	33	21	94	4	75	94
6							21	43	100	87	88	81	22	8
7							3	11	77	67	87	57	43	47
8							10	52	81	7	38	99	27	76
Sheet3

Cell Formulas
Range	Formula
A1	=SUM(G2:N6)

after deleting row 4:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	1688
2							23	73	88	52	10	12	71	84
3							11	34	80	1	90	88	72	82
4							5	41	33	21	94	4	75	94
5							21	43	100	87	88	81	22	8
6							3	11	77	67	87	57	43	47
7							10	52	81	7	38	99	27	76
Sheet3

Cell Formulas
Range	Formula
A1	=SUM(G2:N5)

But with indirect:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	2220
2							23	73	88	52	10	12	71	84
3							11	34	80	1	90	88	72	82
4							96	11	68	90	83	12	80	92
5							5	41	33	21	94	4	75	94
6							21	43	100	87	88	81	22	8
7							3	11	77	67	87	57	43	47
8							10	52	81	7	38	99	27	76
Sheet3

Cell Formulas
Range	Formula
A1	=SUM(INDIRECT("G2:N6"))

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	2080
2							23	73	88	52	10	12	71	84
3							11	34	80	1	90	88	72	82
4							5	41	33	21	94	4	75	94
5							21	43	100	87	88	81	22	8
6							3	11	77	67	87	57	43	47
7							10	52	81	7	38	99	27	76
Sheet3

Cell Formulas
Range	Formula
A1	=SUM(INDIRECT("G2:N6"))

 

[RasGhul]

So I have a workbook with multiple sheets where the target values are always in same columns.

On my first sheet I have a table that summarizes data from all sheets against different criteria, rows are Sheet Names and Columns are different categories/criteria;

=SUMIFS(INDIRECT("'"&$A2&"'"&"!$E$2:$E$1000"),INDIRECT("'"&$A2&"'"&"!$A$2:$A$1000"),TABLE!B$1)

This code saves me doing a separate sum formula for each sheet range & criteria. I'm sure there's other important uses for indirect but that's one of them. As the code copies down it automatically replaces the sheet name into the formula.

Good Excel indirect vid;

https://www.youtube.com/watch?v=GUClkvJ7Gag

 

[sheetspread]

Indirect can also create variable sized "virtual" arrays like here:

https://www.mrexcel.com/forum/excel-questions/900042-rtrim-formula.html

https://www.mrexcel.com/forum/excel-questions/1048390-sum-top-items-between-two-sheets.html

 

[sheetspread]

And I can't leave without also mentioning:

http://www.contextures.com/xlFunctions05.html