Structured reference to same column in another table. RRS feed

  • Question

  • I have two tables.  The first one contains a row for each Production Order and a column for each Quality Assurance Parameter. The other contains a row for each Production Order and columns for lot's of other stuff about each order.  I've added several columns to the second table with the same names as some of the important Quality Assurance Parameters. I'm looking for a structured reference to simply find and display the QA parameter for the listed parameter in the first table on appropriate row and same column in the second table.

    I can do this easily if I build the name of the column into the formula explicitly, but I really want to use the name in the header row as the mapping so I can use a generic formula because sometimes, the QA parameters of interest will be different.  I want to just be able to change the header row in table two.


    Order  Tensile  Density    AreaWgt

    1          1.4       97           144

    2          1.3       99           152

    3          1.5       96           148

    Table2: (as I want it!)

    Order   OtherFld  OtherFld    Density

    1             xxx1         yyy1       97

    3             xxx2         yyy2       96

    What I'm after are structured references in Table2's [Density] column to pull the 97 and the 96 from Table1.

    I have this right now:

    = index(Table1[Density],match([@Order],Table1[Order],0))

    And it works, but it requires the fieldname 'Density' in the formula.  I want to instead somehow refer to the Header Row of Table2 and use that to map the appropriate column of Table1 so that if I later change the name of that column in Table2, I don't need to update the formulas.

    Any ideas?



    PS - I'm aware that it would be a lot easier if Table 1 were structured as [Order], [QAParamaterName], [Value], but I don't have that option available.  My data source is already pivoted....

    Saturday, February 28, 2015 6:40 PM

All replies

  • Can't you set a reference of Header Row of Table2 to map the appropriate column of Table1?

    Click on the appropriate cell of Table2, click '=', and click on the appropriate cell in Table1.

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Sunday, March 1, 2015 12:36 AM