Pivot tables using multiple tables

[Gringarlow]

I have 2 tables of data, one shows %'s of errors for individual techs, the
other shows details of the techs work that caused the errors. I tried to
build a pivot table using multiple ranges of both table, but can't get the
results I need. I want to show the Tech Name ( Which is a field in both
tables) and the %'s . Then what I was asked to provide was the ability to
click on the name and get the details on a new sheet. I can't figure that
part out.

 

[Jacob Skaria]

Hope the data format of both ranges are same. Refer
http://www.contextures.com/xlPivot08.html

If this post helps click Yes

 

[ryguy7272]

Good call Jacob! I was just going to paste the very same link in here. That
should do it. As an alternative, you may consider manually copying your data
into one consolidated range, make sure the Field names (Headers, or whatever
you want to call them) at the top are the same (data must be in the
appropriate columns).

You can take a look at this if you have time:
http://www.rondebruin.nl/copy2.htm

That will merge everything in your workbook into a Summary Sheet, and then
you can build your Pivot Table off of that (I do that all the time). Again,
make sure the Field names are lined up appropriately. Pivot Tables are great
for summarizing sets of data, but when the data in in Pivot Table format, you
ability to control it is somewhat limited (not so limited, but certainly more
limited than in a Range outside of the Pivot Table).


HTH,
Ryan--

 

[Gringarlow]

The tables do not have the same formats or ranges. One table has 10 columns,
the other 18 columns. Data is very different in both. The only common column
is the Tech Name.

 

[ryguy7272]

Ok, well it will take a little creativity on your part, but you can
definitely do this. Make sure you have Field names (you HAVE TO have names
at the top of each column - make them up to match the criteria under them).
Put the table with 18 columns in a sheet, then place the table with 10
columns under it, with 'Tech Name' in the same column, the other 9 columns
would have to be pushed off to the right, so the first one starts under
Column19 (one past the 18 from the Table above). Both tables should line up
under the Tech Name criteria, or this identifier, or whatever you want to
call it. Then, build the Pivot Table. I'm not doing it right now, but I
would surmise that that would work fine for you.

This is really turning into an Access question. Can you use Access? That
would make this project MUCH easier!!

HTH,
Ryan---

 

[Gringarlow]

Ok, that makes sense, a little more work on my part....
I do use Access, but the company I work for doesn't like Access, so we have
a limited number of users, just me in my dept. Usually I put tables through
Access queries then export the results to excel.......