Hello,
I'm trying to find a formula that will help me find the intersection of a row and a column. I know that you can use Vlookup, or Index/match, or Sumproduct.
In one sheet called SUMMARY I'm trying to find the intersection (the value) of a row called SALES and a column called Current Year for every single company that I have as worksheets within the same file. For example, I have a sheet called GOOGLE, another called Microsoft, another called Netflix. In each sheet, I have Sales $, Earnings, EBITDA, etc all listed in one column. And I have years 2013 to 2022 listed in one row. In my summary sheet, I would have Google, Microsoft and Netflix listed in column A and in column B I would have their corresponding value of their Sales $ for 2018 (or 2019), depending on what year I choose using the formula I'm trying to find.
But my problem is this, my file has different worksheets (over 300 worksheets) of different companies In some of the sheets, the sales reference is in column B, and in others its in column C, and in others its in column D. While the years might be on Row 4, some in Row 10, and some in row 24.....they might be on different rows depending on the sheet is what I'm trying to get at. So my formula has got to be dynamic and account for these changes. I have tried using the index/match formula but this implies that the sales ref would always be in the same column and the years are always on the same row. I'm trying to a formula that will give me the intersection of a specific row and a specific column in variable arrays located in different locations on sheets so I can summarize the sales $$ for a specific year in a sheet called SUMMARY.
Sorry If this explanation is too detailed...let me know if you need more specifics.
I'm trying to find a formula that will help me find the intersection of a row and a column. I know that you can use Vlookup, or Index/match, or Sumproduct.
In one sheet called SUMMARY I'm trying to find the intersection (the value) of a row called SALES and a column called Current Year for every single company that I have as worksheets within the same file. For example, I have a sheet called GOOGLE, another called Microsoft, another called Netflix. In each sheet, I have Sales $, Earnings, EBITDA, etc all listed in one column. And I have years 2013 to 2022 listed in one row. In my summary sheet, I would have Google, Microsoft and Netflix listed in column A and in column B I would have their corresponding value of their Sales $ for 2018 (or 2019), depending on what year I choose using the formula I'm trying to find.
But my problem is this, my file has different worksheets (over 300 worksheets) of different companies In some of the sheets, the sales reference is in column B, and in others its in column C, and in others its in column D. While the years might be on Row 4, some in Row 10, and some in row 24.....they might be on different rows depending on the sheet is what I'm trying to get at. So my formula has got to be dynamic and account for these changes. I have tried using the index/match formula but this implies that the sales ref would always be in the same column and the years are always on the same row. I'm trying to a formula that will give me the intersection of a specific row and a specific column in variable arrays located in different locations on sheets so I can summarize the sales $$ for a specific year in a sheet called SUMMARY.
Sorry If this explanation is too detailed...let me know if you need more specifics.
