Index + Match across multiple sheets AND Indirect?

[ExcelNoob28]

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!

 

[Toadstool]

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.

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
	A	B	C	D
11
12	ACME	ACME	5	ACME
13
Master

Cell Formulas
Range	Formula
A12	A12	=ACME!D1
B12	B12	=INDIRECT("'ACME'!D$1")
C12	C12	=COUNTA(INDIRECT("'ACME'!$A$2:$a$999"))
D12	D12	=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
	B	C	D	E	F	G
1	ACME	No. Investors	Bodgit	No. Investors	Wrench	No. Investors
2	ACME	5	Bodgit	3	Wrench	4
3	Investors	Value	Investors	Value	Investors	Value
4	Inv4	5555	Inv6	222	Inv5	111
5	Inv23	6666	Inv7	333	Inv24	888
6	Inv30	7777	Inv8	444	Inv31	444
7	Inv37	8888			Inv38	345
8	Inv44	9999
9
10
Master

Cell Formulas
Range	Formula
B2,F2,D2	B2	=INDIRECT("'"&B$1&"'!D$1")
C2,G2,E2	C2	=COUNTA(INDIRECT("'"&B$1&"'!$A$2:$a$999"))
B4:B10,F4:F10,D4:D10	B4	=IF(ROW()-ROW($B$3)<=C$2,INDIRECT("'"&B$1&"'!A"&ROW()-ROW(B$3)+1),"")
C4:C10,G4:G10,E4:E10	C4	=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]

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...

 

[Toadstool]

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
	B	C	D							K	L	M	N	O	P	Q
1	B2	Number								Corp		Your Example	With INDIRECT			With Sheet Name and Column as Variables
2	ACME	1								Bodgit		3	3		Where to |INDEX?	$C:$C
3		2								Wrench					Where to MATCH?	$K:$K
4		3								ACME					Sheet name?	3Q20-Ownership
5
6															Result	3
3Q20-Ownership

Cell Formulas
Range	Formula
M2	M2	=INDEX('3Q20-Ownership'!$C:$C,MATCH($B$2,'3Q20-Ownership'!$K:$K,0))
N2	N2	=INDEX(INDIRECT("'3Q20-Ownership'!$C:$C"),MATCH($B$2,INDIRECT("'3Q20-Ownership'!$K:$K"),0))
Q6	Q6	=INDEX(INDIRECT("'"&Q4&"'!"&Q2),MATCH($B$2,INDIRECT("'"&Q4&"'!"&Q3),0))