Summing across multiple sheets using indirect

[Barren_Wuffett]

Hi all, trying to sum up across multiple sheets. Caveat: the cells being added up are all the same cell address as the active cell, just on different sheets. My troubles: I'd like to be able to insert rows or columns across all tabs and have the formula adjust to always be summing the equivalent cell addresses on those tabs. Example below. I'd like to sum from the Pricing >>> tab to the <<< Pricing tab, and if the active cell is B7, I'd like to sum up all B7s across that sheet range.

Thoughts?

 

[bebo021999]

If you wish to get the value from same active cell from sheet "Pricing":
In B7:
=INDIRECT("'Pricing'!"&ADDRESS(ROW(),COLUMN()))

If you wish to sum of range from sheet "Price", i.e, B7:D7 (3 columns from column B)
In B7:
=SUMPRODUCT(SUBTOTAL(9,INDIRECT("'Pricing'!"&ADDRESS(ROW(),COLUMN()+{0,1,2}))))

Book1
	A	B
7	1	33
Sheet1

Cell Formulas
Range	Formula
A7	A7	=INDIRECT("'Pricing'!"&ADDRESS(ROW(),COLUMN()))
B7	B7	=SUMPRODUCT(SUBTOTAL(9,INDIRECT("'Pricing'!"&ADDRESS(ROW(),COLUMN()+{0,1,2}))))

Below image is sheet Pricing:

Book1
	A	B	C	D
7	1	10	11	12
Pricing

BTW, my Excel 2016 does not allow ":" in tab's name. I have no idea how you can do that.

 

[Barren_Wuffett]

If you wish to get the value from same active cell from sheet "Pricing":
In B7:
=INDIRECT("'Pricing'!"&ADDRESS(ROW(),COLUMN()))

If you wish to sum of range from sheet "Price", i.e, B7:D7 (3 columns from column B)
In B7:
=SUMPRODUCT(SUBTOTAL(9,INDIRECT("'Pricing'!"&ADDRESS(ROW(),COLUMN()+{0,1,2}))))

Book1
	A	B
7	1	33
Sheet1

Cell Formulas
Range	Formula
A7	A7	=INDIRECT("'Pricing'!"&ADDRESS(ROW(),COLUMN()))
B7	B7	=SUMPRODUCT(SUBTOTAL(9,INDIRECT("'Pricing'!"&ADDRESS(ROW(),COLUMN()+{0,1,2}))))

Below image is sheet Pricing:

Book1
	A	B	C	D
7	1	10	11	12
Pricing

BTW, my Excel 2016 does not allow ":" in tab's name. I have no idea how you can do that.

Thanks. But the ":" means I'm summing across multiple sheets. That's the issue. The ":" isn't in the tab name, it indicates that I am taking the sum of the same cell across multiple tabs.

So the issue still stands

 

[bebo021999]

OK, I got it.

Book1
	B
1	9
2	11
3	13
4	15
5	17
6	19
7	21
Sheet1

Cell Formulas
Range	Formula
B1:B7	B1	=SUM('Pricing>>>:Pricing<<<'!B1)

Sheet Pricing>>>

Book1
	B
1	1
2	2
3	3
4	4
5	5
6	6
7	7
Pricing>>>

Sheet Pricing<<<

Book1
	B
1	8
2	9
3	10
4	11
5	12
6	13
7	14
Pricing<<<