# 3D Indirect Tab Refer

#### 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.

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) 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"),"")) I know I'm impressed!

Stephan

#### Attachments

• 662 KB Views: 3
• 663.5 KB Views: 2

#### jasonthewinner

##### New Member
Hi there!
Can you explain the differences between the two versions of your 3D Indirect Spreadsheet, one using YEAR date TAB names and the other using alphanumeric TAB names?
Thanks!

#### Stephan

##### Member
Hi there!
Can you explain the differences between the two versions of your 3D Indirect Spreadsheet, one using YEAR date TAB names and the other using alphanumeric TAB names?
Thanks!
Hi, well TABS may include more characters then just year, and instead of lots of equals, the indirect formula directly refers to tab cell even though includes letters other just date numbers.

ALPHANUMBERIC TAB TYPE:
B2 =IF(AND(B\$15="YES"),IF(\$AF2<=\$AB\$1,INDIRECT("'"&TEXT(\$A2,"General")&"'!A\$225"),""))

Helper 2 Columns:
AE2 =VALUE(RIGHT(A2,4))
AF2 =DATE(AE2,1,1)

YEAR TAB TYPE:
B2 =IF(AND(B\$15="YES"),IF(\$A2<=\$AB\$1,INDIRECT("'"&YEAR(\$A2)&"'!A\$225"),""))

• Tetonne