Help with the best way to produce this sheet and report

[inf0rm3r]

Good afternoon folks,

Hoping someone can assist me as I'm a little out of my depth with this one.

I have a marina that has 12 Meter Boxes and has an array of 30 vessels that I need to track their electrical usage for reporting and billing. I'm finding this quite difficult to do in excel even with data validation (lists of vessels and meters) and using pivot tables to generate the data reporting. I think it might be the way I'm presenting the initial data which could be better

diagram1.png

Columns are pictured above, using a pivot table using this method will give me the correct Kw usage per vessel for the month. But I've had to create 12 sheets, one for each meter. The other problem using this method is there needs to be a formula in the used Kw column which calculates the power used overnight between the 2 reading times and if a vessel uses a different meter you get duplicates and have to report manually which defeats the purpose of the sheet.

Would anyone know a way to do a report with this data to report each Vessels Power usage on each Meter Box outputted as a total Kw used for the month?

Below is a sample of the data I'd need to work with.

sample data.png

[Pepe Le Mokko]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[inf0rm3r]

Thanks Pepe,

I have attached the sheet I am working with. There are two tabs I've mocked up data in. One is Sample Data the other is Required Report which is just a manual entry of how I need the data displayed. I've manually calculated the fields here.

Excel sheet is in attachments.

Appreciate the response

[inf0rm3r]

bump bump bump

[JeteMc]

I propose that all the data be placed in one table as modeled and that Excel do all the work of figuring out which vessel used how many Kw using a pivot table linked to a timeline as modeled.
Let us know if you have any questions.