Spend Actuals vs Forecast vs Plan

[avis_1989]

Hello Experts,

I have 3 data sources for cost- Actual, Plan and Forecast. These three data sources are on separate sheets within the same workbook. Length of the columns in all these three sheets are the same. Column A contains the business unit code, Column B contains Cost pool name. The remaining columns contain periodic cost data.

The requirement is -
1. I need to make separate sheets for separate business units, with values from the aforementioned three data sources.
2. The VBA code should be able to input the cost data based on "Row 5". If it is "OpEx Actual", then it should refer OpEx Actual tab, etc.

I have tried to put index match match function, as a work around. But I think this can be streamlined and simplified using VBA. Especially when the number of business units increase in my data set.

Can any of you please help ?

Thanks
Avishek

[TMS]

Why not just make ONE sheet with all business units listed. Then use VLOOKUP or INDEX/MATCH to get the relevant data from each of the sheets. Then you can use an AutoFiter or, maybe better, a Pivot Table to filter the data as required. Separate sheets will be difficult to manage (and create/maintain).

[avis_1989]

Hello,

The requirement is that I create separate sheets for separate business units. I am trying to break the problem into two parts, so that it is easier for me.
1. Adding a vba to create new sheets as per the business unit codes. So for example, if I have 10 business units, vba wil create 10 new sheets with the BU names as sheet name.
2. Adding index match match formula on each sheet to get data from the three source files.

I have already recorded this macro to achieve point#2.

Range("D9").Select
ActiveCell.FormulaR1C1 = _
"=INDEX('OpEx Actual'!R8C4:R65536C20,MATCH('New tab1'!RC1,'OpEx Actual'!R8C1:R65536C1,0),MATCH('New tab1'!R6C,'OpEx Actual'!R3C4:R3C20,0))/1000"
Range("D9").Select
Selection.AutoFill Destination:=Range("D9:D17"), Type:=xlFillValues
Range("D9:D17").Select
ActiveWorkbook.Save

But the problem is that I do not know how to replicate this code for all the tabs for 10 business units. Because I have hardcoded the sheet reference to 'New Tab1'. How do I ask excel to change this reference to the new tabs as and when these new tabs are created ?

Thanks
Avi

[TMS]

For part 2, try (untested):

Please Login or Register  to view this content.