MS Query / Pivottable / NamedRanges question

[DelScorchoSr]

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,

Scott

[Bob Phillips]

I have always used PT with multple ranges directly, never had cause to use MSQuery inbetween. PT allows multiple ranges to be added, from separate sheets.

Have you actually tried it and hit the problem?

[DelScorchoSr]

I have always used PT with multple ranges directly, never had cause to use MSQuery inbetween. PT allows multiple ranges to be added, from separate sheets.

Have you actually tried it and hit the problem?

I have not... (I am an Access VBA programmer - so some of this Excel stuff is new to me).

How do I go about this, do I create the namedranges and then type them into the PT data source?

[Bob Phillips]

You don't even need named ranges.

Just go to Data>Pivot Table and follow the wizrd, it is quite straight-forward.

[Bob Phillips]

I have just done some reading on this, and Debra has a good explanation of the problem that you were referring to (can't say I have hit it, but it does exist). She also shows the workaround using MSQuery, take a look at http://www.contextures.com/xlPivot08.html