Help with Dynamic Worksheet Tab references

[dwb]

I have workbook with over 100 sheets (all identical). Each has extensive calculations to project correct amounts/cash flow over up to 20 years in varying categories (15+). I'm trying to sum each of various categroies (located in a single column of each tab) into an seperate, individual sheet to have simple table to sort, index, etc...

By example, assuming 5 sheets (1,2,3,4,5) on Sheet 6, I have a formula in column A referencing Cell A1 (=Sheet1!A1). I would like to copy this fomula right and down such that in Column B it references Sheet2, cell A1 (=Sheet2!A1) and so on... Basically changing the "Tab" name only. Using find and replace now, which is tedious.

Possible?

 

[etaf]

what are you tab names ?
you maybe able to use
=INDIRECT("sheet"&COLUMN()&"!B2")
if you place in column A - it will use sheet1
as you copy across the columns - then it will reference sheet2 , etc

BUT indirect() is a volatile function and so recalculates

SUM() can also be used as a 3D reference
=SUM(Sheet1:Sheet4!A2)
so will use all the sheets inbetween

Book9
	A	B	C	D
1	123
2	123	sheet2 123	sheet3 a1	sheet4 a1
Sheet1

Cell Formulas
Range	Formula
A2:D2	A2	=INDIRECT("sheet"&COLUMN()&"!A1")

Book9
	A	B	C	D	E
1	123
2	123	sheet2 123	sheet3 a1	sheet4 a1	#REF!
3
4
5
6
7	132
Sheet1

Cell Formulas
Range	Formula
A2:E2	A2	=INDIRECT("sheet"&COLUMN()&"!A1")
A7	A7	=SUM(Sheet1:Sheet4!A2)

Book9
	A
1	sheet2 123
2	2
Sheet2

Book9
	A
1	sheet3 a1
2	3
Sheet3

i have added a dropbox link - but will only be available a few days

ref multiple sheets - ETAF.xlsx

Shared with Dropbox

www.dropbox.com

 

[dwb]

My tab names are usually 3-4 letter/number location names things like NYC, CHC1, TBDC2, etc.. (names like that, but need formula that can handle any legit tab name).

I also need to be able to add individual sheets and additional summary sheets (no the tabs placement and order could change).

I used this formula to pull the tab name into the individual sheets:
=MID(CELL("filename",D1),FIND("]",CELL("filename",D1))+1,255)
However, on the summary sheet(s), to reference the tab name, I still cannot copy accross the 100 columns without using "find and replace" in each instance.

I cannot use 3D Sum because it then "sums" all the cells in any given cell, which isn't what I trying to do. I tried SUMIF accross 3D, but couldn't get it to work using the tab name.

Ideas?