Report data from an excel file in another excel file

Question

Hi everyone,

I am wondering if there is a way to find a data when we only have a reference on the position of that data. I explain:

I have an excel file with the references below:

Year 3

Year 2

Year 1

Salt

(T01, r10, c10)

(T01, r10, c20)

(T01, r10, c30) + (T01, r30, c30)

Pepper

(T01, r20, c10)

(T01, r20, c20)

(T01, r20, c30) + (T01, r30, c30)

In a second excel file, I have the data that I am trying to report on the table above. The format of my second file is as below:

T01

c10

c20

c30

r10

22.14

45.2

45.5

r20

23.5

22.6

58.2

r30

41.3

44.3

Is there a way to report the data from the second table in the first one by only having the references mentioned (T for Table of reference, r for the row, and c for the column)? e.g for example at the end, I would have on the cell (T01, r10, c10) : 22.14

Please note that these are two different excel files.

Thank you for your help,

Loli · Answer

Thanks very much @srvas17 !

@srvas17 @mceleavey , any idea on which tool to use if the data I'm trying to report is included in 2 different sheets within the file? For example, in the following table, columns refer to two different tables T01 and T02 in the attached excel file:

Year 3

Year 2

Year 1

Salt

(T01, r10, c10)

(T02, r10, c20)

Thank you,

Amounts.xlsx

srvas17 · Answer

Cleaned it up by by re-arranging both tables so that it fits a wider use case, hope this helps!

Possible Solution2.yxzp

srvas17 · Answer

This might be a oversimplified approach, but what I did was construct the key in the reference table, then lookup those values to find-replace them in the dataset.  Would welcome an additional solution if someone can come up with a more efficient approach.

Possible Solution.yxzp