Index + Match across multiple sheets AND Indirect?

ExcelNoob28

New Member
Joined
Apr 12, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Really hope someone can help me with this!

I'm not great at Excel and have no idea how to write the following formula. I work for a public company. I created an Excel Spreadsheet with 21 tabs - master sheet and the remaining sheets on 20 competitors (one per sheet). The first column in each of those 20 tabs lists names of the investors in my company's competitors. (A2 to whatever). The second column lists value of the investment (B2 to whatever).

The master sheet lists 20 companies across (C1 to V1), with # Peers Owned in B1, and Investor's name in A1. I know how to do the formula for B1: =COUNTIF(C2:V2, ">0")

Once I compiled all the investors from the 20 sheets and deleted duplicates, there were 700 names left. I figured I'd need to write 700 formulas because the goal is to have the cells in the master sheet pull from the other 20. If/when the data is updated after next quarter (and so on) in those 20 sheets, the master sheet will then automatically update as well, even if there are new investors in a competitor that change the placement of the data in the cells.

On a Zoom call earlier today, I showed my boss what I got so far and the gist of the response was, "Looks great, just use "indirect" and you only need to write one formula."

I have no idea what he's talking about, but if I ask, I'll look like an idiot. Does anyone know what he's talking about?

Please help!
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,169
Office Version
  1. 2016
Platform
  1. Windows
Hi Excelnoob28,

I found it tricky following how your sheets are constructed so let me give a different example to explain INDIRECT.

Excel doesn't handle multiple sheets well. e.g. without using VBA you can't retrieve the sheet names, and some functions won't work across sheets. Let me show an image of 3 sheets for competitors ACME, Wrench and Bodgit.
1618278121274.png


I've repeated the competitor name in D1 of each sheet just as a crosscheck.

Let's assume you wanted to select the D1 of the ACME sheet and pull it into the Master. You would enter an equals sign on the Master then select the ACME tab, click D1 and press Enter so that Master cell would contain =ACME!D1 and you'd see the text "ACME". The challenge is you don't want hard coded cell addressees as you want to do searches, etc so you use INDIRECT.

Here on Master I pulled in that D1 from ACME into A12. In B12 I show an INDIRECT to do the same. In C12 I show how I can count how many ACME Investors are listed and in D12 I use A12 to provide the sheet name.

ExcelNoob28.xlsx
ABCD
11
12ACMEACME5ACME
13
Master
Cell Formulas
RangeFormula
A12A12=ACME!D1
B12B12=INDIRECT("'ACME'!D$1")
C12C12=COUNTA(INDIRECT("'ACME'!$A$2:$a$999"))
D12D12=INDIRECT("'"&A12&"'!D$1")


Let me now expand the example. If I put the competitor names (sheet names) into B1, D1 and F1 then I can pull the Investor Names and Values from each sheet.

ExcelNoob28.xlsx
BCDEFG
1ACMENo. InvestorsBodgitNo. InvestorsWrenchNo. Investors
2ACME5Bodgit3Wrench4
3InvestorsValueInvestorsValueInvestorsValue
4Inv45555Inv6222Inv5111
5Inv236666Inv7333Inv24888
6Inv307777Inv8444Inv31444
7Inv378888  Inv38345
8Inv449999    
9      
10      
Master
Cell Formulas
RangeFormula
B2,F2,D2B2=INDIRECT("'"&B$1&"'!D$1")
C2,G2,E2C2=COUNTA(INDIRECT("'"&B$1&"'!$A$2:$a$999"))
B4:B10,F4:F10,D4:D10B4=IF(ROW()-ROW($B$3)<=C$2,INDIRECT("'"&B$1&"'!A"&ROW()-ROW(B$3)+1),"")
C4:C10,G4:G10,E4:E10C4=IF(ROW()-ROW($B$3)<=C$2,INDIRECT("'"&B$1&"'!b"&ROW()-ROW(B$3)+1),"")



Does that explain how you may use INDIRECT for your workbook?
 

ExcelNoob28

New Member
Joined
Apr 12, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Thank you for taking the time to go through this.

Unfortunately, I don't think it's (fully?) applicable. I had taken a couple of screenshots when he was trying to explain it before (attached). 4Q20 and 3Q20 are examples of other sheets the data is being pulled from.

I just don't know how to integrate that with indirect function...
 

Attachments

  • Sample.jpg
    Sample.jpg
    17.9 KB · Views: 26

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,169
Office Version
  1. 2016
Platform
  1. Windows
With INDIRECT you have to construct the address (sheet name and cell(s)) but it gives you the flexibility to change the function address.

Your first MATCH has no lookup value so I'll demonstrate with the second MATCH

Here's you INDEX/MATCH in cell M2.
Then in N2 is the same but using INDIRECT.
In Q6 is the same but using cell contents to construct the addresses from cells Q2 to Q4.

ExcelNoob28-V2.xlsx
BCDKLMNOPQ
1B2NumberCorpYour ExampleWith INDIRECTWith Sheet Name and Column as Variables
2ACME1Bodgit33Where to |INDEX?$C:$C
32WrenchWhere to MATCH?$K:$K
43ACMESheet name?3Q20-Ownership
5
6Result3
3Q20-Ownership
Cell Formulas
RangeFormula
M2M2=INDEX('3Q20-Ownership'!$C:$C,MATCH($B$2,'3Q20-Ownership'!$K:$K,0))
N2N2=INDEX(INDIRECT("'3Q20-Ownership'!$C:$C"),MATCH($B$2,INDIRECT("'3Q20-Ownership'!$K:$K"),0))
Q6Q6=INDEX(INDIRECT("'"&Q4&"'!"&Q2),MATCH($B$2,INDIRECT("'"&Q4&"'!"&Q3),0))
 

Forum statistics

Threads
1,147,822
Messages
5,743,400
Members
423,792
Latest member
travisds

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top