Hi,
I'm starting to use the Get & Transform Data functionality in Excel 2016, and I'm struggling a bit with what is probably a pretty basic question.
Each of our sales reps maintains a spreadsheet with their information, and I'm looking to combine it into one master spreadsheet. I've got the basic master spreadsheet working, but I have a couple of issues.
Here's how the individual sales reps' spreadsheets appear (obviously dummy data just to give you an idea of the layout - the real spreadsheets have a lot more columns and data, and there are more than 2):
See attached 'daffy duck' and 'joe bloggs' files.
When I combine these in Excel, here's how it comes out: see attached Sales Dashboard file.
This is just a straightforward combine - the only edit I made was to click 'Use first row as headers'.
I have a few issues which I'm struggling with:
- Since there are two header rows in the source spreadsheet, how can I get the filter to appear on the second row? The Filter option is greyed out on the master sheet.
- How can I have the replicate the top row from the source spreadsheet (ie the colour coded merged cells) in the master sheet? Is my best option to remove that row from the master row and add a row at the top manually? Will Excel retain that row each time it refreshes the data?
- How can I stop the header rows appearing at the top of each new source? From the screenshot above you can see that the header rows from the second source spreadsheet are appearing in row 6 and 7.
Apologies for the questions, I'm sure the answers are pretty simple if you know what you're doing, but unfortunately I don't!
Bookmarks