Stephan
Member
Hello
My question is INDIRECT FORMULA that works alphanumeric TAB NAMES (Not just YEAR, but contains Year needed for auto date chk) to automate from TAB NAME in COLUMN A of 2ND TAB (EV YEAR TOTAL), which I have done successfully per Tab Name per YEAR columns B to P, but I want more creative tab names then just YEAR, and will have to use to call TABS year + description within same workbook.
SPREADSHEETs are EV (ELEC VEHICLE) charge/cost/miles summary PER YEAR & SUMMARY YEARS TABS:
2040 & EV2041. TABs are identical except Name.
2nd Tab is SUMMARY TAB (EV YEAR TOTAL) uses INDEX FORMULA:
* YEAR ONLY EXAMPLE (ROW4): This Formula works as intended when Tab Name is formatted as YEAR only.
A4 Format Cells, Custom, YYYY
COL B: =IF($A4<=$Z$2,INDIRECT("'"&YEAR($A$4)&"'!A$225"),"")
COL C: =IF($A4<=$Z$2,INDIRECT(TEXT($A4,"yyyy")&"!$B$225"),"")
* DESCRIPTION + YEAR EXAMPLE (ROW5): Also A5 Format Cells, Custom, "EV"YYYY.
Date display relevant results still work, but as you can see from ROW5 COLUMNS C onwards data is not presented as Formula needs correct edit for more complicated TAB name then basic YEAR only.
COL B: =IF($A5<=$Z$2,INDIRECT("'"&YEAR($A$4)&"'!A$225"),"")
COL C: =IF($A5<=$Z$2,INDIRECT(TEXT($A5,"yyyy")&"!$B$225"),"")
Q. Hence what is Formula for TAB NAME REF when other then just YEAR (YYYY)?
For example EV2041 (So date IF Formula still works)
Hence Formula method to support numbers/letters/spaces.
Thanks in advance
Stephan
My question is INDIRECT FORMULA that works alphanumeric TAB NAMES (Not just YEAR, but contains Year needed for auto date chk) to automate from TAB NAME in COLUMN A of 2ND TAB (EV YEAR TOTAL), which I have done successfully per Tab Name per YEAR columns B to P, but I want more creative tab names then just YEAR, and will have to use to call TABS year + description within same workbook.
SPREADSHEETs are EV (ELEC VEHICLE) charge/cost/miles summary PER YEAR & SUMMARY YEARS TABS:
2040 & EV2041. TABs are identical except Name.
2nd Tab is SUMMARY TAB (EV YEAR TOTAL) uses INDEX FORMULA:
* YEAR ONLY EXAMPLE (ROW4): This Formula works as intended when Tab Name is formatted as YEAR only.
A4 Format Cells, Custom, YYYY
COL B: =IF($A4<=$Z$2,INDIRECT("'"&YEAR($A$4)&"'!A$225"),"")
COL C: =IF($A4<=$Z$2,INDIRECT(TEXT($A4,"yyyy")&"!$B$225"),"")
* DESCRIPTION + YEAR EXAMPLE (ROW5): Also A5 Format Cells, Custom, "EV"YYYY.
Date display relevant results still work, but as you can see from ROW5 COLUMNS C onwards data is not presented as Formula needs correct edit for more complicated TAB name then basic YEAR only.
COL B: =IF($A5<=$Z$2,INDIRECT("'"&YEAR($A$4)&"'!A$225"),"")
COL C: =IF($A5<=$Z$2,INDIRECT(TEXT($A5,"yyyy")&"!$B$225"),"")
Q. Hence what is Formula for TAB NAME REF when other then just YEAR (YYYY)?
For example EV2041 (So date IF Formula still works)
Hence Formula method to support numbers/letters/spaces.
Thanks in advance
Stephan