Prerequisite Reporting

[dzoretic]

Looking for help in how to create a report with data that is located in multiple worksheets.

I want to see how many entities I need from different sources to accomplish a high-level task (this should be an accumulation of all entities):
A = ####
B = ####
C = ####
D = ####

I want to see any prerequisite activities that need to be taken care of to accomplish tasks:
ACTIVITY 1: A = ####, B = ####, C = ####, D = ####
ACTIVITY 3: A = ####, B = ####, C = ####, D = ####
ACTIVITY 2: A = ####, B = ####, C = ####, D = ####
Etc….

The prerequisite tasks should be displayed in the correct order to accomplish task; example - Activity 3 must be finished before Activity 2


I have all the raw data (ie. The ###’s for A, B, C and D) in 26 worksheets. Each sheet has 40 rows representing A, B, C, and D ###’s for task requirement. Each row also has the ACTIVITY prerequisite listed (the ACTIVITY will come from 1 of the 26 worksheets) but the information should probably be coded different (any suggestions / advice would be appreciated on this would be helpful as well)

I have some VBA experience but this is way out of my league!

Thank you

 

[Skybluekid]

Welcome to the Board.

Please could you update your profile with what version of Excel you are using. I guess that you could usr formulas to pull the information. Are you able topost some data?

 

[dzoretic]

Thanks for the quick response. I am running version 16.69 on a MAC. Attached is an extract of data. Columns A - D need to be static while Column E (all the prerequisites) can be modified to accommodate the necessary coding / formulas. Below are examples from 4 sheets:

EXAMPLE.xlsx
	A	B	C	D	E	F
1	Phase	A	B	C	D	Requirements
2	18	4,400,000	4,800,000	4,900,000	4,800,000	W P17
3	19	6,300,000	7,000,000	7,200,000	7,000,000	W P18, E P18
4	20	8,090,000	9,200,000	9,500,000	9,300,000	W P19
5	21	10,400,000	12,000,000	12,000,000	12,000,000	W P20, S P20
6	22	13,300,000	16,000,000	16,000,000	16,000,000	W P21
7	23	17,100,000	21,000,000	21,000,000	21,000,000	W P22, B P22
8	24	22,000,000	27,000,000	28,000,000	28,000,000	W P23, ST P23
9	25	28,200,000	36,000,000	37,000,000	36,000,000	W P24, AC P24
10	26	69,660,000	88,200,000	91,800,000	90,000,000	W P25, WT P25
11	27	116,200,000	148,000,000	152,000,000	148,000,000	W P26, RS P26
K

EXAMPLE.xlsx
	A	B	C	D	E	F
1	Phase	A	B	C	D	Requirements
2	18	1,800,000	1,900,000	2,000,000	1,900,000	K P18
3	19	2,500,000	2,800,000	2,900,000	2,800,000	K P19
4	20	3,200,000	3,700,000	3,800,000	3,700,000	K P20
5	21	4,200,000	4,800,000	5,000,000	4,900,000	K P21
6	22	5,300,000	6,300,000	6,500,000	6,400,000	K P22
7	23	6.900.000	8,300,000	8,600,000	8,400,000	K P23
8	24	8,800,000	11,000,000	11,000,000	11,000,000	K P24
9	25	11,000,000	14,000,000	15,000,000	14,000,000	K P25
10	26	27,000,000	36,000,000	36,000,000	36,000,000	K P26, M P26
11	27	49,680,000	62,640,000	64,800,000	64,800,000	K P27, M P27
ST

EXAMPLE.xlsx
	A	B	C	D	E	F
1	Phase	A	B	C	D	Requirements
2	18	440,000	480,000	490,000	0	K P18
3	19	630,000	700,000	720,000	0	K P19
4	20	810,000	920,000	950,000	0	K P20
5	21	1,000,000	1,200,000	1,200,000	0	K P21
6	22	1,300,000	1,600,000	1,600,000	0	K P22
7	23	1,700,000	2,100,000	2,100,000	0	K P23
8	24	2,200,000	2,700,000	2,800,000	0	K P24
9	25	2,800,000	3,600,000	3,700,000	0	K P25
10	26	6,454,500	8,109,500	8,440,500	0	K P26
11	27	9,599,000	12,247,000	12,578,000	0	K P27
M

EXAMPLE.xlsx
	A	B	C	D	E	F
1	Phase	A	B	C	D	Requirements
2	18	1,300,000	1,400,000	1,500,000	1,400,000	K P18
3	19	1,900,000	2,100,000	2,200,000	2,100,000	K P19
4	20	2,400,000	2,700,000	2,800,000	2,800,000	K P20
5	21	3,100,000	3,600,000	3,700,000	3,600,000	K P21
6	22	4,000,000	4,700,000	4,900,000	4,800,000	K P22
7	23	5,100,000	6,200,000	6,400,000	6,300,000	K P23
8	24	6,600,000	8,200,000	8,400,000	8,300,000	K P24
9	25	8,500,000	11,000,000	11,000,000	11,000,000	K P25
10	26	21,600,000	27,000,000	27,000,000	27,000,000	K P26, AC P26
11	27	35,870,000	46,420,000	48,530,000	46,420,000	K P27, AC P27
RS