Index/Match? Problem with unique sheets with unique column identifiers

[eddievontonsil]

G'day

First post here.

Trying to use INDEX/MATCH to retrieve data, whilst using INDIRECT to incorporate the unique sheet numbers (approx 30 sheets) & unique project numbers (approx. 120 in total)

Summary sheet

In this example, the source data needs to be retrieved from Sheet 7553 below.

[TABLE="width: 750"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Unique identifier[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Branch[/TD]
[TD]Response[/TD]
[TD]Project[/TD]
[TD]1[/TD]
[TD]Project number [/TD]
[TD]Project name[/TD]
[TD]Budget FTE[/TD]
[TD]Budget $[/TD]
[/TR]
[TR]
[TD]733c[/TD]
[TD]7553[/TD]
[TD]23829/001[/TD]
[TD]2[/TD]
[TD]733c/7553/23829/001[/TD]
[TD]Basic costs[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]733c[/TD]
[TD]7553[/TD]
[TD]23829/001[/TD]
[TD]3[/TD]
[TD]733c/7553/23829/001[/TD]
[TD]Discretionary costs[/TD]
[TD]N/A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]Total costs
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]733c[/TD]
[TD]7553[/TD]
[TD]23943/222[/TD]
[TD]5[/TD]
[TD]733c/7553/23943/222[/TD]
[TD]Basic costs[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]733c[/TD]
[TD]7553[/TD]
[TD]23943/222[/TD]
[TD]6[/TD]
[TD]733c/7553/23943/222[/TD]
[TD]Discretionary costs[/TD]
[TD]N/A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7[/TD]
[TD]Total costs[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Note = the reconciliation has the Budget FTE (Full time equivalent - staff numbers) on the X-axis & the response provided has the Budget on the Y-axis. Hoping this does not pose a problem.

I need to prepare retrieve formulas for cells C2 & C5 (FTE data) and D2, D3, D5 & D6 (Budget $)

Response sheet - 7553

Branch name = 733c
Sheet name = 7553
Project name = Row 2

[TABLE="width: 750"]
<tbody>[TR]
[TD]Unique identifier
CONCATENATE
[/TD]
[TD][/TD]
[TD][/TD]
[TD]733c/7553/23829/001[/TD]
[TD]733c/7553/23943/222[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]Project number[/TD]
[TD]Project number[/TD]
[TD]Project number[/TD]
[TD]Project number[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]Category[/TD]
[TD]23829/001[/TD]
[TD]23943/222[/TD]
[TD]XXXXX/XXX[/TD]
[TD]XXXXX/XXX[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]FTE[/TD]
[TD]1.4[/TD]
[TD]0.5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4[/TD]
[TD]Basic costs[/TD]
[TD]$14,000[/TD]
[TD]$5,000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5[/TD]
[TD]Discretionary costs[/TD]
[TD]$7,500[/TD]
[TD]$1,200[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6[/TD]
[TD]Total costs[/TD]
[TD]$21,500[/TD]
[TD]$6,200[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Note: I only need to retrieve data where there is where there is a project number populated in Row 2. However, the responses can include up to ten (10) projects that need to be retrieved.

Solution?
I started out thinking I could use INDEX/MATCH, however I can't seen to crack the issue of having the varying volume of unique projects to retrieve onto the Summary sheet.

I was thinking INDIRECT would also be handy, however; not sure how to incorporate this in a dynamic range of unique project numbers. Perhaps a completely different approach is required? I'm all ears!

I've got myself into a tangle & grateful for any suggestions, including overhauling the approach to the reconciliation on the summary sheet.

Thanks in advance legends!

 

[eddievontonsil]

Would really appreciate any input to this question. Thanks very much!