Dear experts i have some tedious task of creating multiple files below is what I am looking for some automation
Column A to K is the Input file in Raw Data tab
Step1: Column L have to get updated by picking all the information before"." of Column J
Step2: Column M have to get updated by vlookup from the mapping file however if I add more rows in Mapping file it have to get updated
Step3: Column N have to get updated by picking all the information after"." of Column J
Step4: Column O have to get updated by vlookup from the mapping file however if I add more rows in Mapping file it have to get updated
Step5: Column P have to get updated by vlookup from the mapping file however if I add more rows in Mapping file it have to get updated
Once the Raw data sheet is ready it have to create a Pivot as shown in the example
and create separate files based on the sales unit name
1) Convert your data to a Table - select any cell within the raw data and press Ctrl + T to create Table. This should show the data area to be converted and should just need OK. When new entries are added to the table the formulas in L to P will copy down automatically.
2) For your lookups change the formula to look at whole columns on lookup sheet, any additions are then already included.
3) In your Pivot select Sales Unit as a Report Filter/Page Value, top left pane at the bottom of the Fields List navigator pane. When the Pivot has the Page Filter set go to the Pivot Table Tools Ribbon and select Options tab. At the left hand end there is an Options button with a dropdown. Don't click the Options button as this just opens the Table Options window. Select the dropdown and choose "Show Report Filter Pages...". This opens a Window and will show a list of item for which you have Report Pages, it should be just Sales Unit. Click OK and a new sheet will be created with a Report for each Sales Unit. If you then want each of these as a separate file, use the Right Click menu on the tab to Move or Copy.