Tracking invoice numbers across different billing status throughout the month

S

Silha83

New Member
Joined
Feb 6, 2014
Messages
7
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.

[TABLE="width: 665"]
<tbody>[TR]
[TD="colspan: 4"]MONDAY, 1 OCTOBER
[/TD]
[TD][/TD]
[TD="colspan: 4"]THURSDAY, 12 OCTOBER
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD="colspan: 3"]Project Status
[/TD]
[TD][/TD]
[TD]
[/TD]
[TD="colspan: 3"]Project Status
[/TD]
[/TR]
[TR]
[TD]Project No.
[/TD]
[TD]Active
[/TD]
[TD]Delivered
[/TD]
[TD]Pending Review
[/TD]
[TD][/TD]
[TD]Project No.
[/TD]
[TD]Active
[/TD]
[TD]Delivered
[/TD]
[TD]Pending Review
[/TD]
[/TR]
[TR]
[TD]123456
[/TD]
[TD]
[/TD]
[TD]£20
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD]589745
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD]£30
[/TD]
[/TR]
[TR]
[TD]78910
[/TD]
[TD]£10
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD]56895
[/TD]
[TD]£15
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]11121314
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]£50
[/TD]
[TD][/TD]
[TD]11121314
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]£50
[/TD]
[/TR]
[TR]
[TD]15161
[/TD]
[TD]£70
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD]235869
[/TD]
[TD][/TD]
[TD]£85
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1898752
[/TD]
[TD]
[/TD]
[TD]£56
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD]1898752
[/TD]
[TD]
[/TD]
[TD]£56
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]THURSDAY, 4 OCTOBER
[/TD]
[TD][/TD]
[TD="colspan: 4"]MONDAY, 16 OCTOBER
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD="colspan: 3"]Project Status
[/TD]
[TD][/TD]
[TD]
[/TD]
[TD="colspan: 3"]Project Status
[/TD]
[/TR]
[TR]
[TD]Project No.
[/TD]
[TD]Active
[/TD]
[TD]Delivered
[/TD]
[TD]Pending Review
[/TD]
[TD][/TD]
[TD]Project No.
[/TD]
[TD]Active
[/TD]
[TD]Delivered
[/TD]
[TD]Pending Review
[/TD]
[/TR]
[TR]
[TD]123456
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD]£20
[/TD]
[TD][/TD]
[TD]589644
[/TD]
[TD]
[/TD]
[TD]£20
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]78910
[/TD]
[TD][/TD]
[TD]£10
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD]56895
[/TD]
[TD]
[/TD]
[TD]£15
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]11121314
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]£50
[/TD]
[TD][/TD]
[TD]11121314
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]£50
[/TD]
[/TR]
[TR]
[TD]15161
[/TD]
[TD][/TD]
[TD]
[/TD]
[TD]£70
[/TD]
[TD][/TD]
[TD]235869
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]£85
[/TD]
[/TR]
[TR]
[TD]1898752
[/TD]
[TD]
[/TD]
[TD]£56
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD]1898752
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]£56
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]MONDAY, 8 OCTOBER
[/TD]
[TD][/TD]
[TD="colspan: 4"]THURSDAY, 20 OCTOBER
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD="colspan: 3"]Project Status
[/TD]
[TD][/TD]
[TD]
[/TD]
[TD="colspan: 3"]Project Status
[/TD]
[/TR]
[TR]
[TD]Project No.
[/TD]
[TD]Active
[/TD]
[TD]Delivered
[/TD]
[TD]Pending Review
[/TD]
[TD][/TD]
[TD]Project No.
[/TD]
[TD]Active
[/TD]
[TD]Delivered
[/TD]
[TD]Pending Review
[/TD]
[/TR]
[TR]
[TD]589745
[/TD]
[TD]
[/TD]
[TD]£30
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD]589644
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD]£20
[/TD]
[/TR]
[TR]
[TD]78910
[/TD]
[TD][/TD]
[TD]
[/TD]
[TD]£10
[/TD]
[TD][/TD]
[TD]56895
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]£15
[/TD]
[/TR]
[TR]
[TD]11121314
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]£50
[/TD]
[TD][/TD]
[TD]52874
[/TD]
[TD]£50
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]235869
[/TD]
[TD]£85
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD]54258
[/TD]
[TD]
[/TD]
[TD]£24
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1898752
[/TD]
[TD]
[/TD]
[TD]£56
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD]458236
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]£6
[/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="width: 550"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Project No's[/TD]
[TD="align: right"]01-Oct[/TD]
[TD="align: right"]04-Oct[/TD]
[TD="align: right"]08-Oct[/TD]
[TD="align: right"]12-Oct[/TD]
[TD="align: right"]16-Oct[/TD]
[TD="align: right"]20-Oct[/TD]
[/TR]
[TR]
[TD="align: right"]123456[/TD]
[TD]Delivered[/TD]
[TD]Pending Review[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]78910[/TD]
[TD]Active[/TD]
[TD]Delivered[/TD]
[TD]Pending Review[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]11121314[/TD]
[TD]Pending Review[/TD]
[TD]Pending Review[/TD]
[TD]Pending Review[/TD]
[TD]Pending Review[/TD]
[TD]Pending Review[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]15161[/TD]
[TD] [/TD]
[TD]Pending Review[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]1898752[/TD]
[TD]Delivered[/TD]
[TD]Delivered[/TD]
[TD]Delivered[/TD]
[TD]Delivered[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]589745[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Delivered[/TD]
[TD]Pending Review[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]235869[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Delivered[/TD]
[TD]Active[/TD]
[TD]Pending Review[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]56895[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Active[/TD]
[TD]Delivered[/TD]
[TD]Pending Review[/TD]
[/TR]
[TR]
[TD="align: right"]589644[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Delivered[/TD]
[TD]Pending Review[/TD]
[/TR]
[TR]
[TD="align: right"]52874[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD="align: right"]54258[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Delivered[/TD]
[/TR]
[TR]
[TD="align: right"]458236[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Pending Review[/TD]
[/TR]
</tbody>[/TABLE]

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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
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! :-)
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,237
Messages
6,170,924
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top