Excel Formulas (Indirect(index(match)))

[ctone00]

Hello,

I have two worksheets, TestFund (First below) and "Aggregate" second image below.  I am trying to transpose the info from the TestFund worksheet in the "Aggregate" worksheet and i am currently doing so with a sumif formula.   My problem is i want to use a formula that can pull all the information in without my having to update the last argument in the sumif formula.  as an example in the aggregate tab my formula pulls row from TestFund!$7:$7 the TestFund tab.  when i paste the formula over into column C i have to go into the sumif formula and change the row reference to TestFund!$8:$8.  I know there is a way to do this with using indirect and index(match) but i do not know how to put the formula together.  

Any help would be greatly appreciated.

I

[#2]

I'm not sure if this is just a learning thing or maybe just an example as what I see could easily done with 1 formula. The whole range on aggregate A16:E20 could be this formula in A16:
=TRANSPOSE(TestFund!A6:E10)
I really don't see the need for SUMIF but lets assume a very generic case and using just index and match:
=index($B$7:$E$10, match($A17,$A$7:$A$10,0), match(B$16, $B$6:$E$6, 0) )

[#3]

@mtarler Thank you for your response.  This is a closed and simplified example but imagine I have "TestFund(n)" worksheets,  all in the same format as "TestFund".  In the "Aggregate" worksheet i will have TestFund(N) columns * testFund(n):Investors.  Im trying to understand how i can drag a formula across the aggregate worksheet such that it references "TestFund(n)" tab, find the row the investor is sitting on, matches the income type, and pulls in that number.  

I hope this is helpful.  

[#4]

a couple questions:
are you using excel365
are all the tables on the TestFund(n) worksheets laid out the same on the same ranges
then you can use VSTACK(TestFund(1):TestFund(100)!A1:Z100) to stack all the data from all the sheets to do the searches on.
You can also add each of the sheets to the data model and then use a pivot table or use power query
That all said it is hard to give more specific answer without a sample sheet