INDIRECT FORMULA AUTOMATE, other then just YEAR (YYYY)

Hello, sample file attached.


My question is FORMULA 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 (So auto date criteria still works) + 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.

EV photo.png

Cheers


Stephan

Are you still using XL2003 as stated in your profile? If not, amend your profile accordingly as answers are tailored to the OP's version. Thx

Hello.

Excel 2003.

or

Excel 2021 Formulas.

Just prefer Xls as menu simple layout & not hidden either!

Cheers