Forum Discussion
Integration of worksheets and dashboard in Excel
Hi,
We are working on a project to track the ships arriving and departing from our port. We currently run a simple spreadsheet to track the ships coming and ensure they have completed the required forms.
Attached is our workbook that we do this through.
At the moment we can make "yes" for columns such as pre-arrival for received, or hot work completed. We then have to manually check the prices and add it to the invoice column.
I have managed to build out the form to allow the automatic adding of hot work charges etc.
Rules that we have to follow:
We have ships calling to the port wharves. These cannot be levied for berthing but do pay for hot work and engine immobilisation.
We levy ships that anchor (that then do not call to the port) or those that anchor. The rates differ between cruise ships and cargo ships. Cargo ships have 2 different rates.
Is there a way to then have this information automatically transfer through to the invoice template?
Is there a way to build a dashboard to show when ships have booked and are using the anchorages? There are a couple of us working on this so want to ensure that we don't double up a booking.
We have other activities that are charged. Is there a way to get this data through to the invoice template sheet?
I am envisaging that there is method to send the information to be sent to the invoice template once it is ready.
Sorry, I forgot to attach the changes
Sorry, I forgot to attach the changes
Thanks for this! Ideal solution for our team requirements. Appreciate your time and clear / detailed explanations on the "How to" and structures.
Thanks for this.
I think there is a behaviour error.
For ships that do not call to PMNZ facilities, we do charge them the appropriate fee for anchoring. See row 8 - CruiseShip1. This should have found that this is a fee of $4200 (column R is calculating this) and added it to column AH. Is this a formula error that is preventing it showing through?
I put the columns M & N in to try and calculate out those ships that do not get charged for berthing as they call to PMNZ facilities compared to those that just anchor and are available to be charged.
Would conditional formatting allow the row for a ship to completely grey out once the ship has departed?
For the invoicing side, the spreadsheet looks good and works.
For the projects, that have p/wk or p/hr, is there a way to be able to add those hours/weeks in and it auto-calculate?
I've added some comments to the sheets to try and explain what I mean.
The anchoring dashboard - Great!!
I've had a play with moving the ships to the various anchorages and for some reason they don't seem to be moving across into the current actual section?
To get the data into the invoice template on the original workbook. Does it need both the shipping board and the invoicing board to send the data to a final "round-up" table before then going on to the template when required?
Regarding Invoice
Yes, it's combining data from the Shipping Board and The Invoice Book.
The blue section of the "Working table" is getting data from the Invoice sheet and the Yellow bits are getting data from the Shipping BoardI did have values for labour, I called it "Staff Costs" but you can change the wording. You could have a formula that concatenates text and values
="Labour: "&X7&" hrs at $"&AA7&"/Hour"I don't know where to get the p/wk values from?
The invoice lines are built up using a single FILTER formula that looks in the working block and filters to only pull lines where they are marked as "Yes"
=FILTER(N10:Q17, O10:O17="Yes")The FILTER column is a separate table that checks if the row has data in it, that's because each invoice will have a varying number of lines depending on what was charged.
You can use the SLICER to show only "Select" lines to make the invoice neater
Lastly, please give kudos if you feel this has been helpful.
Cheers
MarkRegarding the Conditional Formatting, yes easily done, I have applied it to my latest update.
Regarding the Anchorage Charge, it does work, but it required you to say "No" in column AAI added a "blank" entry on the lookup table as it only had Yes and No,
so now if you leave column AA blank, it will find the 1 in column S
Hi
Regarding the dashboard, I think it's becuase you changed a formula to say "Arrived" where I had "Here"
On the dashboard sheet, modify the formula in X4 to say "Arrived" and copy it down for each row and that should fix it.
This formula is getting a list of booking references for all ships berthed in that location AND where the status is HERE.The dashboard uses the Reference Number in X4 to lookup the ship details
Hi
I have done a bit of a revamp of the sheet for you, which will hopefully help a bit.Well done on getting this far in Excel, it's always a bit daunting when you are trying to do something like this.
please see attached.
In Summary, the changes I have made are:
1. Converted the main tables and the lookup data tables into formal Excel Tables. There are huge benefits from using TABLES in excel, and I'd encourage you to look at youtube videos on using tables. You will see the formulas in the table, use column names instead of cell references, making it easier to understand the formula.2. I have modified the Trial Shipping board, converting the range to a table and included some extra columns that you can fill out for the dashboard, these are to do with actual/expected dates. You will see shaded columns, these are because they contain formulas so should not be overwritten. NOTE: changing a formula in an excel table automatically applies it to all rows in the table.
3. The Invoice Book sheet. I have also converted the range to a table. I have changed the lookup formulas to reference the new lookup tables and shaded the formula columns4. The Data tables sheets have been converted to tables and lookup ranges created for easier dropdown lists.
5. The dashboard has been modified, to include a view that shows "who's currently in", "who's due next" and also highlight if there are clashes. This gets the data from the Trial Shipping board, so are ALL formulas, so no data entry here aside from changing the reporting date which is set to today() date.
6. The invoice bit was not clear to me as I couldn't reconcile between data on the Invoice Book and the Shipping board data. I have pulled data from both of these but I suspect that the values are not quite what you wanted. I have also had to add an Invoice Ref column on the Shipping board data, to relate the 2 sources of data.
Lot's more to do, but I'm out of time, so please review and get back to me with questions.See attached
good luck.
MarkHi,
There are several worksheets in your sample workbook.
Which sheets are your raw datas?
Which one is your expected result?
I think you need to address that question to Jake as he created the original. I have just tried to make it work based on what he has shared, but there are more questions for him to answer
