INDIRECT with INDEX(MATCH())?

[k3yn0t3]

Hi there.
Can anyone help me with an indirect with nested index match formula that's dynamic enough to allow me to get values from other sheets which change depending on the Factory and Metric I need them for?

My hope is to have one formula that's dynamic to copy it across and down as the file has 20 metrics, 20 years, and 200+ different factory sheets. Please see tables below for context. My XL2BB add-in isn't working, sadly.

I'd massively appreciate anyone's help on this. Thank you in advance for taking a look.

Best,
Sam

Here is a snip of how the workbook's tabs are laid out:

On the "Assumed Rates" tab, I need to fill in the blue cells based on the following:

• Factory [#] (name format is consistent...i have 25 factory tabs in all)
• Metric (ex: "Widgets Sold - % Growth YoY", and "Average Price - % Growth YoY")
• Year (we're doing this for a 20-year period)

Assumed Rates

Widgets Sold - % Growth YoY
	Factory 1	--%	--%	--%	--%
	Factory 2	--%	--%	--%	--%
	Factory 3	--%	--%	--%	--%
Average Price - % Growth YoY
	Factory 1	--%	--%	--%	--%
	Factory 2	--%	--%	--%	--%
	Factory 3	--%	--%	--%	--%

Factory 1
***Note: there are many other metrics (and rows generally) on this sheet, have simplified here
***Note: the tab name and metric name are exactly the same (same letters & symbols) on every "Factory [#]" tab...Also, the metric names are all in the same column on left-hand side, while the years are listed across a row at the top.

Stats							2016	2017	2018	2019	2020	2020	2021	2022	2023
Widgets Sold - % Growth YoY							2.0%	2.0%	2.0%	2.0%	2.0%	2.0%	2.0%	2.0%	2.0%
Average Price - % Growth YoY							2.0%	2.0%	2.0%	2.0%	2.0%	2.0%	2.0%	2.0%	2.0%

 

[Peter_SSs]

My XL2BB add-in isn't working, sadly.

In what way is it not working? What goes wrong and at what point of the process does that happen?

 

[k3yn0t3]

In what way is it not working? What goes wrong and at what point of the process does that happen?

I have restarted, uninstalled and reinstalled, etc. a couple times. I also tried dropping the file directly into the ADD-ins folder that excel brings me to when I click "browse" in add-ins menu.

None of the excel workbooks I'm using are protected so I have no clue what that error is coming from?

What always happens is: I go to options, add-ins, enable it from menu, and this pops up:

Then I click "OK" and this pops up immediately:

 

[Peter_SSs]

See if this helps.

 

[Glenn Kennedy]

Me too. Same error message.

 

[k3yn0t3]

Me too. Same error message.

It worked, Glenn! Thanks, Peter!

Sam

 

[k3yn0t3]

Hi there.
Can anyone help me with an indirect with nested index match formula that's dynamic enough to allow me to get values from other sheets which change depending on the Factory and Metric I need them for?

My hope is to have one formula that's dynamic to copy it across and down as the file has 20 metrics, 20 years, and 200+ different factory sheets. Please see tables below for context. My XL2BB add-in isn't working, sadly.

I'd massively appreciate anyone's help on this. Thank you in advance for taking a look.

Best,
Sam

Here is a snip of how the workbook's tabs are laid out:
View attachment 99948

On the "Assumed Rates" tab, I need to fill in the blue cells based on the following:

• Factory [#] (name format is consistent...i have 25 factory tabs in all)
• Metric (ex: "Widgets Sold - % Growth YoY", and "Average Price - % Growth YoY")
• Year (we're doing this for a 20-year period)

Factories_v16_xl2bb.xlsx
	C	D	E	F	G	H	I	J
14					2016A	2017A	2018A	2019A
15	Widgets Sold - % Growth YoY
16	Unit 1				--%	--%	--%	--%
17		Base
18		Downside
19		Stress Case
20
21	Factory 2				--%	--%	--%	--%
22		Mgmt
23		No growth
24		Mid
25
26	Factory 3				--%	--%	--%	--%
27		Mgmt
28		No growth
29		Mid
Assumed Rates

Cell Formulas
Range	Formula
G14:J14	G14	=+G9
G16:J16,G26:J26,G21:J21	G16	=+OFFSET(G16,$E$5,)

Factory 1
***Note: there are many other metrics (and rows generally) on this sheet, have simplified here
***Note: the tab name and metric name are exactly the same (same letters & symbols) on every "Factory [#]" tab...Also, the metric names are all in the same column on left-hand side, while the years are listed across a row at the top.

Factories_v16_xl2bb.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
71	Stats
72	Widgets Sold - % Growth YoY						9.5%	4.1%	4.1%	33.2%	12.8%	2.4%	2.4%	2.0%
73	Average Price - % Growth YoY						9.5%	4.1%	4.1%	33.2%	12.8%	2.4%	2.4%	2.0%
Factory 1

Can anyone help me now that i've updated using xl2bb? See quoted text above (i dropped in the code!)

 

[k3yn0t3]

Respectfully bumping as this is urgent and would massively appreciate any help!

 

[Fluff]

In future please do not post your reply inside quotes, also do not post the minisheet inside a table. It makes a mess of everything & makes it hard to read/copy.

 

[k3yn0t3]

Thanks, Fluff. Will ensure I'm doing it that way in future.