Using a Master sheet to populate multiple sheets with partially overlapping column headers

Question

Hi,

I am trying to use excel streamline some tedious data entry. Basically, I have to document individual events by date and some other parameters of interest in separate columns/categories for each one. Some of these entries will then need to be entered separately on sheet 2 if X outcome occurred, others will need to be entered on sheet 3 if Y occurs, another on sheet 4 if Z, etc. Some may need to be on one, all of them, or any combination in-between.

These separate sheets have a significant amount of overlap between the columns/categories that need to be filled out each but each also has its own unique categories that are not on the others. The overlap of categories that need to be filled out between each sheet is significant enough to make entering the data on each sheet very tedious.

So, I was trying to find a way to have a single master sheet with all the relevant columns/categories, so I could enter all of the data for one event on one line in one sheet and have it populate smaller sheets based on a one or more key codes for each relevant sub-sheet it would need to go to, like a separate category/column with letter codes X, Y, or Z as letter codes for data needing to go to sheet X, Y, or Z) . Even if I I have to copy over all of the columns/headers from the master sheets on to the separate ones and just delete the columns/categories that are not relevant for that sub-sheet, it would still save a lot of time. This is well outside my knowledge base, however. Does anyone have any thoughts?

Answer 1

PowerQuery.

• Put all of your data in the single master / input tab.
• Use Power Query to import all of the data
• Duplicate the original query, to create separate queries for each separate tab
• rename them with descriptive names
• In each query query delete unwanted columns
• apply any filters to specific columns
• Close and load all to tabs
• add new data master / input tab
• right click on any output tab
• select refresh all.

If you need to generate summary reports you can use PivotTables from the resulting output tables.

.

Let us know if you need more detailed instructions