0

I have two tables, each on its own worksheet, that are linked to Sharepoint lists, where one table has a foreign key that maps to the other's primary key. E.g.:

sheet: flavors
+----+------------+------+
| ID |   Flavor   | kCal |
+----+------------+------+
|  1 | chocolate  |   45 |
|  2 | vanilla    |   20 |
|  3 | strawberry |   38 |
+----+------------+------+

sheet: orders
+----+----------+
| ID | flavorID |
+----+----------+
|  1 |       1  |
|  2 |       3  |
|  3 |       2  |
|  4 |       1  |
|  5 |       3  |
+----+----------+

I'd like to join these tables on a third sheet. Because these are linked tables, the size of each table will vary over time. I know that flavors has a one-to-many relationship with orders, so I thought about having putting a formulas so that:

  • column A=orders.ID
  • column B=vlookup of flavors.Flavor
  • column C=vlookup of flavors.kCal

But that would require putting ~3 million formulas on the join sheet, and I'd rather not have to run all those calculations if I only have a couple rows in each list. Is there a way to set up the join table so that it's updated when I refresh the data, but not slowed down by a massive number of excess calculations?

0

You can use PowerPivot, a free download for Excel 2010 to create that scenario with joined tables.

Download link for the add-in (make sure you get the 2010 version) http://office.microsoft.com/en-001/excel/download-powerpivot-HA101959985.aspx

Creating a lookup relationship between two tables is described here: http://office.microsoft.com/en-001/excel/download-powerpivot-HA101959985.aspx

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.