Monthly invoice tracker- is there a better way to arrange the data?

I have developed a Monthly Invoice tracker for the services we offer to the client. I am looking to explore whether there is a better way to arrange the data so that i can do away with merged cells and hence pull reports through a pivot table or formula.

Currently, the sheet has 8 columns; out of which 4 columns are standard (Service Line, Resource, Assigned FTE, Rate/hr) and the next 4 which are variable as per monthly basis are, Hours, Cost, Invoice number, Invoice amount.

The monthly invoices vary in the sense that they can be a combination of any services (as per the client cost centre) for any particular month. Hence every invoice varies as per the services which are worked in that particular month. Also, the resources could move from one service to other.

Please see the attachment, which gives a brief idea about what is the current state and how i wish it should be (check 'Help needed' points).

Any suggestions are greatly appreciated.

Thanks,
Mahesh

Hi John,

I need the invoice number & the invoice amount to be mentioned only once. I am OK if this can be achieved with some helper columns (if needed).

But the tracker should show which service costs (i.e. the invoice amount) are apportioned to which invoice number for the respective month(s).

I will have to share the tracker every month with the client. Hence having the invoice numbers repeated with the amounts also appearing repeatedly is not the way i am looking at.

Thanks for your help anyways. But is a better approach possible? Anyone else would like to help please.

Mahesh

Originally Posted by samba_ravi

Please show your expected results based on Post No2 [Jhon Topley]

Hi Ravi,

I am sorry but am not able to visualise the expected results and hence i posted on this forum to explore if some expert could guide me to arrange or represent the data in a better way..

If i may ask you to please see my attachment, which could help in some way to understand my expectations.

Thanks,
Mahesh

Hi John,

I may be wrong here, but is there a way to use any formula or conditional formatting (which color codes the individual service lines & the costs associated with these services) that make up the Invoice amount.
AND
then we pull up this data in another tab showing in a single row showing the Month, Invoice Number, Invoice Amount and the service costs which make up the invoice amount? Next step, we can think how to get the resources mapped to the costs (i.e. service lines which they worked for).

Just a wild thought, but I want to make this automated to extract the monthly invoices along with the supporting costs for the month.

I am sure there could be a way around. My focus is to complete Step 1 & then may be Step 2 or 3...

Thanks,
Mahesh

The tracker which i intend to make or automate is not going to be an "Invoice template". The Invoices will be generated thro the system. These invoices do not have the details which are in the tracker.

The idea behind this exercise is- because the client wants us to provide a detailed breakdown of the costs; i need to go thro this route of having the Invoices tracked against the respective services utilized and i cant think of any better way than using Excel spreadsheet.

I am sorry again; but this does not serve the purpose of what i am looking for.

Thanks,
Mahesh

Dear experts,

In continuation with the post discussion, here is the update from my end:

I have modified the arrangement for the Invoice tracker & could come close to my expectations of having the Invoice number & the Invoice amount to appear only once while recording the data information.

Now, i need help with 2 things:

1. Given the attached sheet, i need a formula to auto populate the details marked in green cells (in relation to the data in orange highlighted cells)
2. I need a Pivot table set-up (on a new sheet) which gives me a window to see the key details (Invoice Number, Invoice amount, Service costs (which make up the invoice amount), Month, SoW, FTEs & Invoice status.

The data in the sheet is largely manually written, but am trying to see, if there is something which could be made fast & automated.

Let me know if my explanation is not clear. I tried to explain in the best way i could & hope i was able to do so.

Also, any other suggestions are welcome to make this tracker to be put to use.

Thanks,
Mahesh

Can someone please see and guide? A bit urgent for me, hence this request

I have now mentioned my expected results in Tab 2 of the attached worksheet (Named -'Need help here')

Can someone please take a look and guide with the formula approach?

Thanks,
Mahesh