I have two worksheets. One has the source table:
And another has an aggregation table:
To get the numbers from the first table to the second I use the following formula:
It basically checks the year in the result table, matches it with the year in the source table to determine the column with the result, then checks Table ID in the result table and matches it with the Table ID in the column B in the source table. If there are multiple entries for the Table ID in the source table, they are summed up.
But now I need to match the column with the Table ID header instead of having a static column B because I need to use the same table to various types of source tables, where Table ID might be in different column.
But I cannot figure out where to put something like
to identify the column.
Could someone help please?
And another has an aggregation table:
To get the numbers from the first table to the second I use the following formula:
Excel Formula:
=SUMPRODUCT(INDEX(FS!$A$2:$Z$250,0,MATCH(C$1,FS!$A$1:$Z$1,0)),--(FS!$B$2:$B$250=$A2))/1000
It basically checks the year in the result table, matches it with the year in the source table to determine the column with the result, then checks Table ID in the result table and matches it with the Table ID in the column B in the source table. If there are multiple entries for the Table ID in the source table, they are summed up.
But now I need to match the column with the Table ID header instead of having a static column B because I need to use the same table to various types of source tables, where Table ID might be in different column.
But I cannot figure out where to put something like
Excel Formula:
MATCH("Table ID", FS!$A$1:$Z$1,0)
Could someone help please?