Hi,
I have to files:
I have to files:
- XXX.xlsx - File with one tab per department (A, B, C), each with amounts per GL Accounts and months.
- Report.xlsx - FIle with two tabs, one report tab and one tab with GL accounts per phase stored in a table (T_PhaseGL).
The Report referances the XXX.xlsx file, but the file changes every month. So I want to create a dynamic report, where I add the file name in cell B3 and the period I want in B4. I have created a a formula that handles this for specific accounts.
=INDEX(INDIRECT("["&$B$3&"]"&$A7&"!$B$4:$M$12");MATCH(700000;INDIRECT("["&$B$3&"]"&$A7&"!$A$4:$A$12");0);MATCH($B$4;INDIRECT("["&$B$3&"]"&$A7&"!$B$3:$M$3")))
However, I have to add it manually for each GL account I want to include for each phase.
So my plan was to add all the accounts in the T_PhaseGL that I want for each phase. I was hoping to use a SUMPRODUCT formula that checks if the GL account in XXX.xlsx is listed in T_PhaseGL[Tender], struggeling to find a solutions where there are multiple matches.
Can anyone help me find a solution?