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

SUMIFS on table: variable field/column AND variable table BUT without INDIRECT

Fred_2406

New Member
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
MONTH_ENDMAPPING_REFEURKEUR
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.
 
Fred_2406
As all other new members,
You too should read Forum Rules .... before start to post.
Please, read ... reread those and
continue with hints, You've gotten.
 
Fred_2406
As all other new members,
You too should read Forum Rules .... before start to post.
Please, read ... reread those and
continue with hints, You've gotten.

Sorry,

I do not see what I did wrong, from your Forum Rules.

never mind, have a nice evening!

Frédéric
 
Fred_2406
Did I write that You has done something wrong? ... did I ?
I tried to give hints for You.

Those are not mine rules, those are Forum Rules.
As You have read those,
then You notice How to get the Best Results at Chandoo.org ...
There are points, which You would use to get the Best Results.
eg if there are bolded twice one term ... then that would be something useful.
 
I do not see what I did wrong, from your Forum Rules.

Fred_2406

IF You didn't make any wrong .. who to make any post without excel file as example ?!!!
It is not possible to help without submitting a supported file with a sufficient explanation of what is required ... as it is not possible to work on guesswork and to avoid wasting teachers ’time without benefit or importance.
 
Back
Top