• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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.

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:p12, 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
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

I know I'm impressed!

Stephan
 

Attachments

  • EV Range AlphaNumeric Tabs 3D Indirect#.xls
    662 KB · Views: 3
  • EV Range Year Tabs 3D Indirect#.xls
    663.5 KB · Views: 2
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 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"),""))
 
Back
Top