Planning Template

Status
Not open for further replies.

exce101

New Member
Joined
Jan 27, 2018
Messages
14
Office Version
  1. 365
Platform
  1. Windows
Hi guys,

I am in the process of running a planning cycle and have currently I have managed to design what the template will look like for one area i.e. EMEA.

We have numerous areas which have multiple cost centres which are further broken down into accounts at hierarchy levels 1 and 2.

I am unable to attach my file as my work has blocked the upload of files but have tried to describe the contents of the tabs below:

MASTER:

  • This is where all the cost centres will be listed by region/area (I have only put a small sample)
  • Column A has the list of cost centres
  • Column B specifies the region / area
  • Columns F and G have the list of accounts which will be mapped to each cost centre – this list will not change - there is a set of 21 items in Column F and G

EMEA:

  • This is the template I have managed to create based on the EMEA cost centres as per the MASTER tab
  • Column A shows the region
  • Column B lists the Cost Centres
  • Column C shows the allocation rule (please ignore this as I will just apply a vlookup to a mapping table)
  • Columns D and E show the set list of accounts to be included with each cost centre as per the MASTER tab columns F and G
  • Column F and G will be pulling in the amounts from a SAP query applying a sumifs formula by picking up the amount from column BA based on criteria such as the Profit Centre (column AM), Hier 1 (Column BJ) and Hier 2 (Column BK). If this could be included in the macro, that would be fantastic.
  • The layout from columns H to EI will be the same for all regions.

Raw Data:

  • This tab will contain the SAP data in the set out format


What I am trying to achieve is a macro automation where the macro will crease a tab for each region in the set format and include the number of cost centres for the respective regions in the template as per the layout in the EMEA temple. So effectively rathat that having to create 21 rows for each cost centre (and there could be over a 100 cost centres for each area), i would like the macro to do this for me.

So for example currently the EMEA region has 7 cost centres, I would like the macro to create a similar template for the Americas region in a separate tab for the 21 cost centres in this region.



Appreciate any help!

Thanks in advance.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Status
Not open for further replies.

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top