Hi,

I am aware of how to use OFFSET function for building dynamic pivot tables. However I was wondering if there is a way to build the dynamic table from 2 sheets where both of the sheets have the same set of defined columns. Allow me to elaborate

Sheet1
Name Age Location Profession
Jack 10 NY IT
Jill 12 NY IT
Mary 12 NY IT

Sheet1
Name Age Location Profession
Tom 10 NY IT
Tim 12 NY IT


So if I wanted to do a dynamic pivot table on Sheet1, I would do =offset($a$1,0,0,counta($a$1:$a:$60000),4). This works fine, whenever a new row is added.

The reason I need to put use two sheets is because they are being pulled from different sources.

Even it is possible to do it by merging by macros I am willing to go that route. Right now I manually copy from Sheet 2 onto Sheet1 (after Sheet1's data gets updated)