Hello,
I am trying to build SUMIFS functions upon tables, with variable fields and variable tables.
For instance:
I have 2 trial balances tables: T_BG_2019 and T_BG_2018 --> the required value is selected in, say, A5, which is called "FISCAL_YEAR"
I have 2 fields to show amounts: EUR (euro) and KEUR (000's euros) --> the required value is selected in, say, B5 which is called "CURRENCY"
I have several other fields in the table, among which a "MAPPING_REFERENCE".
example: 2019 table
The idea being to use only one P&L format to show a combination of : 2018 in EUR, 2018 in KEUR, 2019 in EUR, 2019 in KEUR
At the moment, I use a volatile function which makes the workbook very slow, as follows:
=SUMIFS(INDIRECT(FISCAL_YEAR&"["&CURRENCY&"]",INDIRECT(FISCAL_YEAR&"["&MAPPING_REFERENCE&"]","TURNOVER")
where I show the calculated total turnover, either in EUR or KEUR, either for 2018 or 2019.
It is very easy to use named ranges for fields, but I run into errors every time I try it for table names.
Any ideas, as to how to avoid INDIRECT and refer to tables by named ranges?
Thank you in advance.
I am trying to build SUMIFS functions upon tables, with variable fields and variable tables.
For instance:
I have 2 trial balances tables: T_BG_2019 and T_BG_2018 --> the required value is selected in, say, A5, which is called "FISCAL_YEAR"
I have 2 fields to show amounts: EUR (euro) and KEUR (000's euros) --> the required value is selected in, say, B5 which is called "CURRENCY"
I have several other fields in the table, among which a "MAPPING_REFERENCE".
example: 2019 table
MONTH_END | MAPPING_REF | EUR | KEUR |
31.01.2019 | TURNOVER | 1,586,523 | 1,587 |
28.02.2019 | TURNOVER | 1,056,333 | 1,056 |
The idea being to use only one P&L format to show a combination of : 2018 in EUR, 2018 in KEUR, 2019 in EUR, 2019 in KEUR
At the moment, I use a volatile function which makes the workbook very slow, as follows:
=SUMIFS(INDIRECT(FISCAL_YEAR&"["&CURRENCY&"]",INDIRECT(FISCAL_YEAR&"["&MAPPING_REFERENCE&"]","TURNOVER")
where I show the calculated total turnover, either in EUR or KEUR, either for 2018 or 2019.
It is very easy to use named ranges for fields, but I run into errors every time I try it for table names.
Any ideas, as to how to avoid INDIRECT and refer to tables by named ranges?
Thank you in advance.