MS Query / Pivottable / NamedRanges question

[LinkFire2]

I have a workbook with 13 worksheets, each containing identically formatted (fields are identical as well), but with different amounts of records.

I all 13 worksheets to feed one pivottable in the same workbook.


I've read that MS query can combine all 13 namedranges (worksheets) and could feed the single pivottable.

(I also read that combining namedranges without using MS Query will not provide the same results to the pivottable as if the data were pulled off of one worksheet, but MS Query can trick it out.)


Can I use multiple input worksheets and MS Query all in the same workbook?

Is there a better way to do this? - my constraint is that it all needs to be in the same workbook


Thanks,

 

[Fazza]

OP,

I'm busy this month so not much time to respond. Suggest you search old thread posts for "multiple source pivot table". There are many threads along the same lines. I've posted many times. Reading a few will confirm your comments. MS Query can do this. Without MS Query, the PT consolidation of ranges is not the same. MS Query per se is not the only way but other ways are simply other implementions of same idea - for example ADO in VBA. MS Query and variants will use SQL of form "SELECT * FROM source1 UNION ALL SELECT * FROM source2 etc etc"

Can all be done in the one workbook. Better than MS Query is using VBA+ADO+SQL. Disadvantage of MS Query is when the file location changes though this can be overcome via VBA. Just set the pivotcache connection to suit.

Defined names if used can not be dynamic, BTW. Alternative to defined names is worksheet names, such as "SELECT * FROM [Sheet1$]".

Old threads should address all this & more.

This one should help too http://www.mrexcel.com/forum/showthread.php?t=315768
Just noticed, thread number has same digits as this one, just different order!!

HTH. Regards, Fazza