Using indirect (?) within table to create dynamic formulas

Hi all,

I'm using Excel 2010, and I'm having a problem within a table that I believe can be solved by using indirect(), but I can't quite figure it out. I have one master table (Table1 in the attachment) that feeds a pivot table. Table1 tracks the performance of several different revenue streams over many weeks, as well as budget vs actual. Table1 is fed by several smaller tables (eg Table3 and Table4). Because data comes into Table3 and Table4 via different sources, the formats of Table3 and Table4 are different. The problem, then, is getting the data into a uniform source so that the performance of each revenue stream can be tracked in one location, Table1. My way to fix this is to write a formula for D4:D7 that would look at the Revenue Stream in column C and evaluate the corresponding formula from Table2. In other words, D4:D5 are related to the "Catalog" revenue stream, so the formula that D4:D5 would evaluate is in G4: =VLOOKUP([@Week],Table3[#All],2,FALSE). D6:D7 are related to the "Promotion" revenue stream, so the formula to evaluate is in G6: =SUMIF(Table4[Week],[@Week],Table4[Revenue]). I'm thinking the format would be do a vlookup for the value in column C, the lookup array would be Table2, the column would be 2, and somehow indirect() would turn the string into a usable formula.

A couple additional thoughts:

-I don't want to use nested if statements as in: if (Rev Stream = Catalog,VLOOKUP([@Week],Table3[#All],2,FALSE),if(Rev Stream = Promotion ... etc. because there are several more revenue streams; the nested if statement would be incredibly cumbersome.
-In this example, Table3 and Table4 are very similar in format, but the actual workbook has a great variety in the individual table formats. Making one uniform format among Table 3, Table 4, etc is not possible.

Thanks in advance for your thoughts.
Cory

Indirect.xlsx