title: Consolidate multiple tables in Excel to one master table from different worksheets
body: <p>I have multiple worksheets with tables on each sheet. Each sheet is a different location for our stores. In each table we have what model computer we are selling and the id number for that computer. One problem is that some of the devices don't have ID numbers listed. I need to create a pivot table where each location has a list with a count of all of the models they are selling at that location.</p>
  
  <p>The tables look like this:</p>
  
  <p>Location  |  Device Type |  Device ID</p>
  
  <p>Seattle   |  HP Pavilion |  ABCD123</p>
  
  <p>Seattle   |  HP Pavilion |  CDFF123 </p>
  
  <p>What I tried to do before is copy and paste them all to one sheet and make a master table where I am able to make the pivot table the way I want. The problem is, if I update one of the sheets the pivot table doesn't update with it.</p>
  
  <p>Then I tried to use the data model but I couldn't seem to get that to work. I think the problem with that was that I had to use the primary key and the foreign key, however, there wasn't necessarily a relation between any two sheets. All of the ID's were unique on each worksheet, but they weren't associated to another ID on another page and each location doesn't necessarily have all of the device types so that didn't work either. </p>
  
  <p>Then I tried to follow this <a href="http://msdn.microsoft.com/en-us/library/gg413464%28v=sql.110%29.aspx" rel="nofollow">http://msdn.microsoft.com/en-us/library/gg413464%28v=sql.110%29.aspx</a> and create a linked table. This will update when I change the original tables. However, how can I make a pivot table from these linked tables?</p>
  
  <p>The final pivot table should look something like this:</p>
  
  <p>&nbsp;&nbsp;&nbsp;Seattle</p>
  
  <p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;HP Pavilion--->12</p>
  
  <p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Dell D630--->5</p>
  
  <p>&nbsp;&nbsp;&nbsp;Dallas</p>
  
  <p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;HP Pavilion--->4</p>
  
  <p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Dell D630--->10</p>
  
  <p>There will be more device types and many more locations but this is the general idea.</p>
  
  <p>I have also looked into using some of the power pivot features but I don't really know where to begin with those. </p>
  
  <p>Any help would be very much appreciated, even if it just pointing me in the right direction.</p>