Forum Discussion
ctone00
Dec 22, 2023Copper Contributor
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...
ctone00
Dec 23, 2023Copper Contributor
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.
mtarler
Dec 23, 2023Silver Contributor
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
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