Stephan
Member
Hello
3D Indirect Spreadsheet (Auto sums of all relevant tabs) totals from several tabs in 1 summary sheet simply by tab name in column A of tab EV YEAR TOTAL.
Further to recent previous post: https://chandoo.org/forum/threads/tab-name-indirect-automate-instead-of-year.51737/#post-294657
I Solved it myself, was surprised how easy it was in the end as I originally thought there must be a alphanumeric way of recoginising tab names then sorting into date, and surely enough there is. Intrigued if there are 1 cell tab name > date for 3D indirect, but this seems v easy, like sumif totals.
2 versions attached, both 3D Indirect Tabs summary, 1 using YEAR date TAB names only, and the other using Alphanumberic TAB names. It was some simple formula edit in the end, Text General instead of Year, as I originally asked.
See formulas in EV YEAR TOTAL rows B2
12, for differences between the 2 versions:
ALPHANUMBERIC TAB TYPE:
B2 =IF(AND(B$15="YES"),IF($AF2<=$AB$1,INDIRECT("'"&TEXT($A2,"General")&"'!A$225"),""))
C2 =IF(AND(C$15="YES"),IF($AF2<=$AB$1,INDIRECT(TEXT($A2,"yyyy")&"!$B$225"),""))
Alphanumeric 3D Indirect Tabs, fixed with few helper columns for Column A2:A12 using Value & Date (AE2:AF12).
AE2 =VALUE(RIGHT(A2,4))
AF2 =DATE(AE2,1,1)
![83381 83381](https://chandoo.org/forum/data/attachments/83/83473-c10ddf9640931f6553ca4e22213e5253.jpg)
Both files have DATE pre check, that I've set in EV YEAR TOTAL (summary tab) cell AB1.
YEAR TAB TYPE:
B2 =IF(AND(B$15="YES"),IF($A2<=$AB$1,INDIRECT("'"&YEAR($A2)&"'!A$225"),""))
C2 =IF(AND(C$15="YES"),IF($A2<=$AB$1,INDIRECT(TEXT($A2,"yyyy")&"!$B$225"),""))
![83382 83382](https://chandoo.org/forum/data/attachments/83/83474-b04a38ec48d8223c90aef7ac0056c786.jpg)
I know I'm impressed!
Stephan
3D Indirect Spreadsheet (Auto sums of all relevant tabs) totals from several tabs in 1 summary sheet simply by tab name in column A of tab EV YEAR TOTAL.
Further to recent previous post: https://chandoo.org/forum/threads/tab-name-indirect-automate-instead-of-year.51737/#post-294657
I Solved it myself, was surprised how easy it was in the end as I originally thought there must be a alphanumeric way of recoginising tab names then sorting into date, and surely enough there is. Intrigued if there are 1 cell tab name > date for 3D indirect, but this seems v easy, like sumif totals.
2 versions attached, both 3D Indirect Tabs summary, 1 using YEAR date TAB names only, and the other using Alphanumberic TAB names. It was some simple formula edit in the end, Text General instead of Year, as I originally asked.
See formulas in EV YEAR TOTAL rows B2
ALPHANUMBERIC TAB TYPE:
B2 =IF(AND(B$15="YES"),IF($AF2<=$AB$1,INDIRECT("'"&TEXT($A2,"General")&"'!A$225"),""))
C2 =IF(AND(C$15="YES"),IF($AF2<=$AB$1,INDIRECT(TEXT($A2,"yyyy")&"!$B$225"),""))
Alphanumeric 3D Indirect Tabs, fixed with few helper columns for Column A2:A12 using Value & Date (AE2:AF12).
AE2 =VALUE(RIGHT(A2,4))
AF2 =DATE(AE2,1,1)
![83381 83381](https://chandoo.org/forum/data/attachments/83/83473-c10ddf9640931f6553ca4e22213e5253.jpg)
Both files have DATE pre check, that I've set in EV YEAR TOTAL (summary tab) cell AB1.
YEAR TAB TYPE:
B2 =IF(AND(B$15="YES"),IF($A2<=$AB$1,INDIRECT("'"&YEAR($A2)&"'!A$225"),""))
C2 =IF(AND(C$15="YES"),IF($A2<=$AB$1,INDIRECT(TEXT($A2,"yyyy")&"!$B$225"),""))
![83382 83382](https://chandoo.org/forum/data/attachments/83/83474-b04a38ec48d8223c90aef7ac0056c786.jpg)
I know I'm impressed!
Stephan