Dynamic Lookup Tables

[Pestomania]

I cannot upload the excel sheet like I was hoping to be able to, but I will add screenshots of what I am working with.

I am hoping to create dynamic formulas that update automatically based on table changes.

Please see and let me know if there are questions.

Picture 1 is the table that I need to change:

Picture 2 is the table providing the "cycle time" for each product to use the individual machine.

Picture 3 is the product demand per month:

Essentially I want the system to look at picture 3, multiply by picture 2, and populate picture 1

 

[Eric W]

Maybe something like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		2023
2	Machine Name	2023 Total	Jan-2023	Feb-2023	Mar-2023
3	Machine 1	6250	500	500	500
4	Machine 2	180000	1600	1600	161600
5
6
7
8	Program Name	Machine 1	Machine 2	Machine 3
9	Product 1	25	80	1000
10	Product 2		16000
11
12
13
14
15		2023
16	Product Name	2023 Total	Jan-2023	Feb-2023	Mar-2023
17	Product 1	250	20	20	20
18	Product 2	10	0	0	10
19
Sheet1

Cell Formulas
Range	Formula
B3:E4	B3	=SUMPRODUCT(INDEX($B$9:$F$13,0,MATCH($A3,$B$8:$F$8,0)),INDEX($B$17:$G$21,0,MATCH(B$2,$B$16:$G$16,0)))

This assumes that the product order in the 2 secondary tables is the same. You can let the table ranges in the formula extend beyond what is currently needed, so if you add more products or dates later, the formula will automatically include them.

 

[Pestomania]

Maybe something like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		2023
2	Machine Name	2023 Total	Jan-2023	Feb-2023	Mar-2023
3	Machine 1	6250	500	500	500
4	Machine 2	180000	1600	1600	161600
5
6
7
8	Program Name	Machine 1	Machine 2	Machine 3
9	Product 1	25	80	1000
10	Product 2		16000
11
12
13
14
15		2023
16	Product Name	2023 Total	Jan-2023	Feb-2023	Mar-2023
17	Product 1	250	20	20	20
18	Product 2	10	0	0	10
19
Sheet1

Cell Formulas
Range	Formula
B3:E4	B3	=SUMPRODUCT(INDEX($B$9:$F$13,0,MATCH($A3,$B$8:$F$8,0)),INDEX($B$17:$G$21,0,MATCH(B$2,$B$16:$G$16,0)))

This assumes that the product order in the 2 secondary tables is the same. You can let the table ranges in the formula extend beyond what is currently needed, so if you add more products or dates later, the formula will automatically include them.

This worked swimmingly, but when I added another column to the table, it didn't change the "Headers" part of the formula. Here is what it looks like now.

=SUMPRODUCT(INDEX(Table19[[Machine 1]:[Machine 3]],0,MATCH($A3,Table19[[#Headers],[Machine 1]:[Machine 3]],0)),INDEX('Monthly Breakdown Table'!$B$3:$DA$4,0,MATCH(B$2,'Monthly Breakdown Table'!$B$2:$DA$2,0)))

I need the Table19[[#Headers],[Machine 1]:[Machine 3]] to go to Table19[[#Headers],[Machine 1]:[Machine 4]]

 

[Eric W]

Just change [Machine 1]:[Machine 3] to [#Data] like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		2023
2	Machine Name	2023 Total	Jan-2023	Feb-2023	Mar-2023
3	Machine 1	6250	500	500	500
4	Machine 2	180000	1600	1600	161600
5
6
7
8	Program Name	Machine 1	Machine 2	Machine 3
9	Product 1	25	80	1000
10	Product 2		16000
11
12
13
14
15		2023
16	Product Name	2023 Total	Jan-2023	Feb-2023	Mar-2023
17	Product 1	250	20	20	20
18	Product 2	10	0	0	10
Sheet1

Cell Formulas
Range	Formula
B3:E4	B3	=SUMPRODUCT(INDEX(Table19[#Data],0,MATCH($A3,Table19[#Headers],0)),INDEX($B$17:$G$18,0,MATCH(B$2,$B$16:$G$16,0)))

The biggest issue is that the 2 secondary tables have to have the same number of rows. If they are not actual Excel tables, you can extend the range beyond the end. But if you use the table nomenclature, you can't do that. So if you add a new product, you have to change the formula. Unless you make table 3 into a table too, then you can use the same trick.

 

[Pestomania]

Awesome! Thank you that seemed to work well!