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

Tab Name Indirect automate instead of YEAR

Status
Not open for further replies.

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.


83333

Thanks in advance


Stephan
 

Attachments

Stephan

Member
Hello

Excel File version 2021 attached, presumably easier to construct compatible Formula format from broader range available in XLSX.

83335
 

Attachments

Last edited:

bosco_yip

Excel Ninja
Or using your original file without changing the sheet name.

Try this formula method as suit with your Excel 2021, by using of XLOOKUP function, the old Excel versions can used Index+Match instead.

1] All header description in result "sheet TOTAL" must be as same as the source data sheet (I copied all header from "sheet 2040" row 222)

2] Add "sheet TOTAL" unit in D3: P3

3) Enter data in "sheet TOTAL" A4:A30, as your criteria value (Lookup value)

4] Create a define name as in:

- Select "sheet TOTAL" B4 >> Formula >> Define Name >>
- Name: CellText
- Refers to: =GET.CELL(53,$A4)

Then,

5] In "sheet TOTAL" B4, formula copied across right and down:

=IFERROR(XLOOKUP(B$1,INDIRECT(CellText&"!$222:$222"),INDIRECT(CellText&"!$225:$225")),"")

Remark: Since the above formula used an old Macro 4 GetCell function, file need to be saved in xlsm type Excel Macro-Enabled Worksheet.

83354
 

Attachments

Last edited:

Stephan

Member
Hello

1st reply, yes use of YEAR as is (YYYY) is best solution.

2nd reply, wow that is detailed/complicated, thank you for explaining!
I'm hoping for simpler solution that would work in Excel2003. Perhaps something similar to Photos Index Match formulas in photo below?
or 3D SumIfs files attached?

83363

83362

You mentioned Index Match as possibility, so for example B5 & BC Formula?

Also in Excel 2021 the xlsm shows no data, I don't know why!
83360

Cheers Stephan
 

Attachments

Last edited:

jasonthewinner

New Member
Hello

1st reply, yes use of YEAR as is (YYYY) is best solution.

2nd reply, wow that is detailed/complicated, thank you for explaining!
I'm hoping for simpler solution that would work in Excel2003.

You mentioned Index Match as possibility, so for example B5 & BC Formula?

Also in Excel 2021 the xlsm shows no data, I don't know why!
View attachment 83360

Cheers Stephan
Hi, so in Excel 2003, you can use the YEAR function to extract the year from a date. For example, if your date is in cell A1, you can use the formula "=YEAR(A1)" in another cell to extract the year.
As for your second issue, you can use the INDEX MATCH formula to look up a value in a table based on another value. For example, if you have a table with dates and corresponding values, and you want to look up the value for a specific date, you can use the formula "=INDEX(B:B, MATCH(A2, A:A, 0))" (assuming your dates are in column A and values are in column B, and the date you want to look up is in cell A2).
Regarding the xlsm file not showing any data, Idk what to suggest. Have you tried opening the file on a different computer or with a different version of Excel?
 

Stephan

Member
Hi, so in Excel 2003, you can use the YEAR function to extract the year from a date. For example, if your date is in cell A1, you can use the formula "=YEAR(A1)" in another cell to extract the year.
As for your second issue, you can use the INDEX MATCH formula to look up a value in a table based on another value. For example, if you have a table with dates and corresponding values, and you want to look up the value for a specific date, you can use the formula "=INDEX(B:B, MATCH(A2, A:A, 0))" (assuming your dates are in column A and values are in column B, and the date you want to look up is in cell A2).
Regarding the xlsm file not showing any data, Idk what to suggest. Have you tried opening the file on a different computer or with a different version of Excel?
Hello

Year? I was referring to TAB NAME as just YYYY, as formula compares date =IF($A5<=$Z$2,.......

Index Match? But if data can be from Various/Numerous Multiple TABS (ie many many many YEARS) how would Index Match work? Isn't Index Match specific to TAB?
Maybe it just longer Formula, but realistically Formula has to refer to TAB YEAR NAME in Column A of EV YEAR TOTAL, so can just copy & paste down to make it generic.

SumIf / SumProduct with Defined Name has 3D methodology of "ALL" tabs, don't my worksheet purpose is not to total all years, but PER YEAR SUMMARY.

Cheers
 

bosco_yip

Excel Ninja
......................
I'm hoping for simpler solution that would work in Excel2003. Perhaps something similar to Photos Index Match formulas in photo below?
or 3D SumIfs files attached?..................
Also in Excel 2021 the xlsm shows no data, I don't know why!
Cheers Stephan
Hi Stephan,
1] As you mentioned in #2, you are using Excel2021 so my formula and attachment is designed for Excel 2021 only.

2] In your Excel2021, you need saved my attachment in xlsm type Excel Macro-Enabled Worksheet, otherwise show no data.

3] Yes, you can use Index+Match instead of Xlookup function in the old Excel 2003 and saved the file in Excel 97-2003 worksheet.

4] As per the forum rule: "One post one question", you need to open a new post for your new requirement/question.

5] This post is closed

Regards
Bosco
 
Status
Not open for further replies.
Top