INDIRECT with INDEX(MATCH())?

k3yn0t3

New Member
Joined
Oct 5, 2023
Messages
42
Office Version
  1. 365
Platform
  1. Windows
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:
1696802509261.png


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.

Stats201620172018201920202020202120222023
Widgets Sold - % Growth YoY2.0%2.0%2.0%2.0%2.0%2.0%2.0%2.0%2.0%
Average Price - % Growth YoY2.0%2.0%2.0%2.0%2.0%2.0%2.0%2.0%2.0%
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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:

1696815460885.png


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

1696815588288.png
 
Last edited:
Upvote 0
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
CDEFGHIJ
142016A2017A2018A2019A
15Widgets Sold - % Growth YoY
16Unit 1--%--%--%--%
17Base
18Downside
19Stress Case
20
21Factory 2--%--%--%--%
22Mgmt
23No growth
24Mid
25
26Factory 3--%--%--%--%
27Mgmt
28No growth
29Mid
Assumed Rates
Cell Formulas
RangeFormula
G14:J14G14=+G9
G16:J16,G26:J26,G21:J21G16=+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
ABCDEFGHIJKLMN
71Stats
72Widgets Sold - % Growth YoY9.5%4.1%4.1%33.2%12.8%2.4%2.4%2.0%
73Average Price - % Growth YoY9.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!)
 
Last edited by a moderator:
Upvote 0
Respectfully bumping as this is urgent and would massively appreciate any help!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,814
Messages
6,181,124
Members
453,021
Latest member
Justyna P

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
Back
Top