Tracking invoice numbers across different billing status throughout the month

[Silha83]

Hi there,

I don't know if a similar question has already been asked on here but I couldn't find it, so I'm sorry if I'm repeating!

My question is this. Twice a week, I produce an invoicing report for our project teams. This details the project number, the billing status and the monetary amount of the project number.

I would like to use these bi-weekly reports across a given month to show how teams have progressed with a particular project by showing the different billing cycles the project has moved through.

MONDAY, 1 OCTOBER		THURSDAY, 12 OCTOBER
	Project Status			Project Status
Project No.	Active	Delivered	Pending Review		Project No.	Active	Delivered	Pending Review
123456		£20			589745			£30
78910	£10				56895	£15
11121314			£50		11121314			£50
15161	£70				235869		£85
1898752		£56			1898752		£56
THURSDAY, 4 OCTOBER		MONDAY, 16 OCTOBER
	Project Status			Project Status
Project No.	Active	Delivered	Pending Review		Project No.	Active	Delivered	Pending Review
123456			£20		589644		£20
78910		£10			56895		£15
11121314			£50		11121314			£50
15161			£70		235869			£85
1898752		£56			1898752			£56
MONDAY, 8 OCTOBER		THURSDAY, 20 OCTOBER
	Project Status			Project Status
Project No.	Active	Delivered	Pending Review		Project No.	Active	Delivered	Pending Review
589745		£30			589644			£20
78910			£10		56895			£15
11121314			£50		52874	£50
235869	£85				54258		£24
1898752		£56			458236			£6

<tbody>
</tbody>

The above data is an example of what I mean. This data shows an example of the type of invoicing records I create (though with much more data!) and the table below shows how I want to organise the data.

Project No's	01-Oct	04-Oct	08-Oct	12-Oct	16-Oct	20-Oct
123456	Delivered	Pending Review
78910	Active	Delivered	Pending Review
11121314	Pending Review	Pending Review	Pending Review	Pending Review	Pending Review
15161		Pending Review
1898752	Delivered	Delivered	Delivered	Delivered
589745			Delivered	Pending Review
235869			Delivered	Active	Pending Review
56895				Active	Delivered	Pending Review
589644					Delivered	Pending Review
52874						Active
54258						Delivered
458236						Pending Review

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

I have already created an automated way of grabbing unique project numbers from the different invoicing reports so I just need help with how I would add the name of the project status in the main are of the table against a particular date/project number, as above.

I hope this makes sense and someone is able to help.

Thank you so much for any thoughts you may have!

Silha

 

[Silha83]

Just to update and clarify...

The invoicing reports are on different worksheets to the analysis, so the project numbers listed on the analysis table are not in the same order as they would be on any given invoicing report.

So, for example, I used this formula =INDEX($B$3:$D$3,,MATCH(MAX(B5:D5),B5:D5,0)) which could work except for the fact that it assumes the project numbers are in the exact same row number in both the analysis table and the specific invoicing report.

Would it be possible to do a v lookup within an index formula?

Thanks everyone!