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%

 

[k3yn0t3]

I figured out a solution to this problem.

I did the below instead and referenced a cell with the sheet title "Factory_1" (# updates dynamically so i could easily duplicate the assumptions section format for each factory. THe cell("address") part of the formula allows the oriignal reference to dynamically update when it is copied across. This worked perfectly because my date headings are laid out the same way in all my sheets.

Thanks, everyone!

=+INDIRECT($A$10&"!"&CELL("ADDRESS",I60))