pull a value with Index-Match formula when the source is changing

[Yakovzim]

I need to present a summary of a P&L report every month in two different currencies, and the source data is a BI report.
I need to sum up by two different categories. The BI report looks like that:

[TABLE="width: 684"]
<colgroup><col><col span="7"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Group Currency#1[/TD]
[TD="colspan: 3"]Group Currency#2[/TD]
[/TR]
[TR]
[TD="align: left"]Item[/TD]
[TD="align: left"]Company[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: left"]Deposits[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]57[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]97[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]92[/TD]
[TD="align: right"]83[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: left"]Loans[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]97[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]93[/TD]
[TD="align: right"]69[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]31[/TD]
[/TR]
[TR]
[TD="align: left"]Exchange rate differences[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]84[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]88
[/TD]
[/TR]
</tbody>[/TABLE]

The example above shows the data from March - every item appears in two different currencies and spread out by month. I'm pulling certain information based on 'concatenate' function which joins the item and the company and on it I use the Index-Match.

The problem is that in the following month (let's say April) I have two new columns (one <gs id="6e2aff71-eef6-4048-944c-470480f8548b" ginger_software_uiphraseguid="db020b36-a346-4824-8c51-2f5eee9615a4" class="GINGER_SOFTWARE_mark">for</gs> Group currency 1 in April and one for GC2 in April). That means that the Index array I defined for let's say 'deposits+company 1000' shifts.

Is there any way to change the index so that it would fit in every month?

Thanks for the help,
Yaakov

Excel <gs id="6b1acd5b-94b7-412e-b2e6-95714420573d" ginger_software_uiphraseguid="bb982d72-143a-4bd8-9325-84b524efb861" class="GINGER_SOFTWARE_mark">ver</gs>. 2010
Win -7

 

[cbatrody]

Hi,

Do you have different tabs for each month or everything is in one tab (worksheet)?