Forum Discussion
Combine data from multiple excel workbooks in a folder into one master file
alex_n Difficult to follow what you are trying to achieve. Can you share a file?
How will the combining work if reports 3 and 4 have new column?
https://1drv.ms/f/s!Aqv4zBSFNKaymn_m8d9SM-0VmzlB?e=d5NNXl
Thanks,
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.