Automatically extract figures from different sheet

[taigorchai]

Extract all data in Summary sheet.xlsx
	B	C	D	E	F	G	H
4		Total Cost	Total Cost	Total Cost	Total Cost	Total Cost	Total Cost
5	Section	Fish	Dog	Cat	Cow	Deer	Total
6	A1	24.00	180.00	336.00	612.00	852.00	2,004.00
7	A2	-	-	-	-	-	-
8	A3	-	-	-	-	-	-
9	A4	-	-	-	-	-	-
10	A5	-	-	-	-	-	-
11
12
13	Expected result
14		Total Cost	Total Cost	Total Cost	Total Cost	Total Cost	Total Cost
15	Section	Fish	Dog	Cat	Cow	Deer	Total
16	A1	90.00	246.00	402.00	678.00	918.00	2,334.00
17	A2	918.00	678.00	246.00	402.00	90.00	2,334.00
18	A3	12.00	24.00	36.00	48.00	60.00	180.00
19	A4	-	-	-	-	-	-
20	A5	-	-	-	-	-	-
Summary

Extract all data in Summary sheet.xlsx
	A	B	C	D	E	F	G	H	I	J
7		Section			Sales
8		A1
9					Fish	Dog	Cat	Cow	Deer	Total
10		Jan-2021			2.00	15.00	28.00	51.00	71.00	167.00
11		Feb-2021			2.00	15.00	28.00	51.00	71.00	167.00
12		Mar-2021			2.00	15.00	28.00	51.00	71.00	167.00
13		Apr-2021			2.00	15.00	28.00	51.00	71.00	167.00
14		May-2021			2.00	15.00	28.00	51.00	71.00	167.00
15		Jun-2021			2.00	15.00	28.00	51.00	71.00	167.00
16		Jul-2021			2.00	15.00	28.00	51.00	71.00	167.00
17		Aug-2021			2.00	15.00	28.00	51.00	71.00	167.00
18		Sep-2021			2.00	15.00	28.00	51.00	71.00	167.00
19		Oct-2021			2.00	15.00	28.00	51.00	71.00	167.00
20		Nov-2021			2.00	15.00	28.00	51.00	71.00	167.00
21		Dec-2021			2.00	15.00	28.00	51.00	71.00	167.00
22	A1	Total Cost		-	24.00	180.00	336.00	612.00	852.00	2,004.00
23
Table

Cells with Data Validation
Cell	Allow	Criteria
B8	List	=Data!$J$3:$J$5

Hello,

In "Table" sheet in cell B3 have drop down list. When select different section the figures in table will auto change. These figures are link from "Data" sheet.

Now my problem is in "Summary" sheet. This sheet is link from "Table" sheet with Index Match formula. When i select different Section in "Table" sheet the figure will auto change accordingly in "Summary" sheet.
In "Summary" sheet is there any way possible to make it to show ALL the figures no matter what i selected from the drop down list in "Table" sheet? so that those figures won't keep changing whenever i selected different section in "Table" sheet.
Those Section not available it will show Nil "-" example Section A4 and A5.

It would be great if can use formula to make it possible. Otherwise, would be fine if use Macro/VBA to solve this problem it.

Note:
1) in "Summary" sheet please pull figures from "Table" sheet directly and do not pull form "Data" sheet.
2) Please do not use Power Query and Office 365 formula version to solve this issue because this Excel file I need to share with non Office 365 user.

Thanks for your help.

 

[Crystalyzer]

Can you post the "Data" worksheet as well?

 

[taigorchai]

Can you post the "Data" worksheet as well?

Hi, thanks for your reply. Here's the "Data" sheet

Extract all data in Summary sheet.xlsx
	A	B	C	D	E	F	G
3		Section	Fish	Dog	Cat	Cow	Deer
4	Jan-2021	A1	2	15	28	51	71
5	Feb-2021	A1	3	16	29	52	72
6	Mar-2021	A1	4	17	30	53	73
7	Apr-2021	A1	5	18	31	54	74
8	May-2021	A1	6	19	32	55	75
9	Jun-2021	A1	7	20	33	56	76
10	Jul-2021	A1	8	21	34	57	77
11	Aug-2021	A1	9	22	35	58	78
12	Sep-2021	A1	10	23	36	59	79
13	Oct-2021	A1	11	24	37	60	80
14	Nov-2021	A1	12	25	38	61	81
15	Dec-2021	A1	13	26	39	62	82
16	Jan-2021	A2	71	51	15	28	2
17	Feb-2021	A2	72	52	16	29	3
18	Mar-2021	A2	73	53	17	30	4
19	Apr-2021	A2	74	54	18	31	5
20	May-2021	A2	75	55	19	32	6
21	Jun-2021	A2	76	56	20	33	7
22	Jul-2021	A2	77	57	21	34	8
23	Aug-2021	A2	78	58	22	35	9
24	Sep-2021	A2	79	59	23	36	10
25	Oct-2021	A2	80	60	24	37	11
26	Nov-2021	A2	81	61	25	38	12
27	Dec-2021	A2	82	62	26	39	13
28	Jan-2021	A3	1	2	3	4	5
29	Feb-2021	A3	1	2	3	4	5
30	Mar-2021	A3	1	2	3	4	5
31	Apr-2021	A3	1	2	3	4	5
32	May-2021	A3	1	2	3	4	5
33	Jun-2021	A3	1	2	3	4	5
34	Jul-2021	A3	1	2	3	4	5
35	Aug-2021	A3	1	2	3	4	5
36	Sep-2021	A3	1	2	3	4	5
37	Oct-2021	A3	1	2	3	4	5
38	Nov-2021	A3	1	2	3	4	5
39	Dec-2021	A3	1	2	3	4	5
Data

 

[Crystalyzer]

I don't believe what you want is possible since you're restriction is that the summary data must be pulled from the Table and not the Data directly. Pulling from the table will ALWAYS change the summary data if you change the Section in the table.

 

[taigorchai]

Hi,

Hmmm.... is it possible to make a macro for automatically select all the Section then automatically copy those Total figures from Table sheet then paste in Summary sheet according to their section?

Thanks.

 

[aRandomHelper]

Why not pull directly from the Data sheet though? The Summary sheet looks more like a summary of the Data sheet than a summary of the Table sheet.