Forum Discussion
Combine data from multiple excel workbooks in a folder into one master file
alex_n Will get back to you tomorrow, if not someone else jumps in.
I cannot find a way where I can populate multiple columns using the name ranges as opposed to populating the entire tab where the contents of the columns will be mixed up as new columns were added with reports 3 and 4. It only lets me populate one name range column at a time.
Here is the parameters list when I invoke custom function:
'Summary' is entire tab
'Contract' through 'Year' are name ranged columns. It is missing 'Overhead' name ranges that was added to report 4.
I believe you need to work with structured tables. In all four files, I formatted each of the data ranges to an Excel table called "Table1". It should like something like this:
Then you can connect PQ to the Reports folder and pick-up Table1 from each file. Make sure that you select report 4 as the sample file. PQ defaults to the first file in the folder. If that one has fewer columns than later ones the new columns will not be picked up.
The attached file should work for you provided you create the tables as mentioned above.
- I have over 100 files that I would need to work to apply the structured tables to. Looks like a VBA approach is a better solution here.