Is there a way to join two data sheets into one pivot?

[golfnut019]

Just wondering if there is a way to do that.




Thanks,

Bryan

 

[golfnut019]

Anyone?

 

[Fazza]

Well, what I do is give each data source a name. (If data will change either use a dynamic named range such as =OFFSET(some cell,,,COUNT(etc.etc) or udpating the range on worksheet deactivation such as

Range("A1").CurrentRegion.Name = "Data1"

)

And then join either by code or manually via MS Query - as in Data, import external data, new database query. Select both tables & the field/s you want, filter as required, and then either go straight to a pivot table or via MS Query to modify the SQL if required. Etc.

HTH
Fazza

 

[Fazza]

post script

Thought again of the SQL as I didn't explain much in the previous post.

If you want all the fields from both tables, then edit the SQL to something like

SELECT *
FROM path.workbook.first table
UNION ALL
SELECT *
FROM path.workbook.second table

Using this approach has the usual limitations of hard coded addresses in SQL. Suggest if this is a problem you create the SQL on the fly.

Running the pivot table wizard should let you select data from different sources, BTW, it is just that I tend to do this via SQL.

regards,
Fazza