Cell Ref to Tab instead of direct tab name, same worksheet.

[Stephan]

Cell Ref contents TAB NAME instead of actual typed TAB NAME?

File attached:
Diesel Edit Year Ref

Tab: Total

Example: ROW 6
COLUMNS: B-M & AL-BC

Formulas in these cells above quote '2020'! as tab to refer to.

However want to ref TAB NAME as in A6, so no INDIVIDUAL EDIT cols B-M & AL-BC.

Hence for each YEAR ROW want to make it a copy & paste row instead of lots of editing. Year tab is prepared template.

Cheers Stephan

 

[Stephan]

In other words what to substitute '2020' with for cell ref of same year:

B6 =IF(A6<=$BG$2,'2020'!$Y$29,"")

 

[Fluff13]

How about
=IF(A6<=$BG$2,INDIRECT("'"&YEAR(A6)&"'!$Y$29"),"")

 

[Stephan]

How about
=IF(A6<=$BG$2,INDIRECT("'"&YEAR(A6)&"'!$Y$29"),"")

Hello thank you for rushing to my aid!

This works for Column B only & my prowess in Excel doesn't know how to edit INDIRECT for Column C onwards:

C6 =IF(A6<=$BG$2,'2020'!$Y$34,"")

CHEERS

 

[Stephan]

Here's a thought, perhaps somebody can suggest viable alternative of using ref to tab for further columns with an edit of:
SUMPRODUCT SUMIF INDIRECT

with Defined Name list of:
Year TABS

Example file attached: 3D SUMIFS
Which I'm unsure how to edit formula, but I feel the answer to this riddle is here, but maybe Choose/Address/Index would work.

The intention is not to add all the tabs (Years) altogether, just "specific" year referenced in Column A in the 1st spreadsheet (Diesel), which is row specific (A certain year per row).

Suggestions invited!

 

[bosco_yip]

Here's a thought, perhaps somebody can suggest viable alternative of using ref to tab for further columns with an edit of:
SUMPRODUCT SUMIF INDIRECT

with Defined Name list of:
Year TABS

Example file attached: 3D SUMIFS
Which I'm unsure how to edit formula, but I feel the answer to this riddle is here, but maybe Choose/Address/Index would work.

The intention is not to add all the tabs (Years) altogether, just "specific" year referenced in Column A in the 1st spreadsheet (Diesel), which is row specific (A certain year per row).

Suggestions invited!

Try........

In "Summary" sheet B3, formula copied across and down :

=SUMPRODUCT(SUMIF(INDIRECT("'"&tabs&"'!A3:A8"),$A3,INDIRECT("'"&tabs&"'!R3C"&COLUMN(B$1),0)))

Regards

 

[Stephan]

Try........

In "Summary" sheet B3, formula copied across and down :

=SUMPRODUCT(SUMIF(INDIRECT("'"&tabs&"'!A3:A8"),$A3,INDIRECT("'"&tabs&"'!R3C"&COLUMN(B$1),0)))

Regards

Hi

Thanks but its the Diesel spreadsheet "TOTAL" tab that needs an edit.

For example on Total what to edit '2020' with A6, INDIRECT FORMULA suggested only worked for COLUMN B:
B6 =IF(A6<=$BG$2,'2020'!$Y$29,"")
C6 =IF(A6<=$BG$2,'2020'!$Y$34

 

[Stephan]

D6
=INDIRECT(TEXT(A6,"yyyy")&"!$V$32")

This worked for Columns B-L, few things:

1. If no data, how to change FALSE to nothing?

2. Columns AL-BC unable to construct working edit for A6 Ref instead of Direct Tab ref. A
lso these are ARRAY FORMULAS that require CTRL, SHIFT & ENTER:

Example in Cell AL6:
=MIN(IF(('2020'!$A$1:'2020'!$A$284="MPG"),('2020'!$B$1:'2020'!$B$284)))

Example in Cell AP6:
=MAX(IF(('2020'!$A$1:'2020'!$A$284="MPG"),('2020'!$B$1:'2020'!$B$284)))