2
Are table relationships only for pivot tables & charts, or can they be used in formulas too? The introduction of tables made it easy to write really readable sumifs like this:
=sumifs(counties[population],counties[state],"NY")
When I create another table with a relationship to counties
, is there any way to use that in my formulas as well? The following doesn't work. (Imagine there is a region
table with state and region columns, and that I've created a relationship between counties
and region
based on state):
=sumifs(counties[population],region[region],"Northeast")
Edit: What I'm interested to know is if the new table relationships feature added in Excel 2013 has any utility outside of pivot tables and charts.
Yes, it seems like MSDN was exaggerating when they said "Now that Excel 2013 has a built-in Data Model, VLOOKUP is obsolete." – user268174 – 2014-09-11T14:32:44.403
PowerPivot allows you to set relationships between tables - if you wanted to you could replace your
SUMIFS
with a PowerPivot table and then your measure would look something like this:=CALCULATE(sum(counties[population]),FILTER(region,region[region]="Northeast"))
. Perhaps I should amend my answer! – Andi Mohr – 2014-09-11T14:47:27.793