Bringing a value from another table without using related

kizofilax

New Member
Joined
Sep 10, 2012
Messages
8
Hello, here is what I have

[TABLE="width: 500"]
<tbody>[TR]
[TD]Case[/TD]
[TD]pk[/TD]
[/TR]
[TR]
[TD]212[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1212[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]444[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]


Table B

[TABLE="width: 500"]
<tbody>[TR]
[TD]pk[/TD]
[TD]category[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]muffin[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]bagel[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]pizza[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]toast[/TD]
[/TR]
</tbody>[/TABLE]


I created a relationship between them and i know i can go to table A and do related(TableB'category'), the problem is that this creates another table and what I want to do is create a pivot table from A and show this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]case[/TD]
[TD]category[/TD]
[/TR]
[TR]
[TD]212[/TD]
[TD]muffin[/TD]
[/TR]
[TR]
[TD]1212[/TD]
[TD]bagel[/TD]
[/TR]
[TR]
[TD]444[/TD]
[TD]muffin[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD]pizza[/TD]
[/TR]
</tbody>[/TABLE]


I need to show this table in this format as a pivot table (people want to see the category for each case)

If I just do this with that relationship what I get is something that looks like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]111[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]bagel[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]muffin[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]pizza[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]toast[/TD]
[/TR]
</tbody>[/TABLE]

Its as if the relationship wasn't being recognized, any idea how to do this?

Thanks
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Hi kizofilax,

As per your sample data, I think you can have the same output using tables. This approach is simpler to construct, smart and expandable, and you don’t have to worry about extending the formulae or expanding the tables.

One simple VLOOKUP() formula is required to bring the category from table B into table A for each pk value.
 
Upvote 0
The issue is that the first table (I'll call it Table A) is on the many side of the relationship and Table B is on the one side. Filtering only ever flows from the one side to the many side. So when you put CASE from Table A in your pivot table rows, it will not filter Table B. When you then add CATEGORY from Table B to rows in your pivot, it is completely unfiltered and hence all values are visible in your Pivot. You could fix this by creating a measure that DOES filter TABLE B. Something like this

=CALCULATE(COUNTROWS(TableA),TableB)

Here is a sample workbook https://dl.dropboxusercontent.com/u/30711565/cross filter.xlsx
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top