Forum Discussion
Excel Formulas (Indirect(index(match)))
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 curre...
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) )
=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) )
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.
- 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
