Forum Discussion

JakeOliver's avatar
JakeOliver
Copper Contributor
Dec 02, 2024
Solved

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 requir...
excel
Excel for web
Formulas and Functions
office 365
JakeOliver's avatar
JakeOliver
Copper Contributor
Dec 03, 2024

Mark_J_W

 

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? 

 

  • Mark_J_Walker's avatar
    Mark_J_Walker
    Brass Contributor
    Dec 03, 2024

    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 Board

    I 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
    Mark


  • Mark_J_Walker's avatar
    Mark_J_Walker
    Brass Contributor
    Dec 03, 2024

    Regarding 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 AA

    I 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

     

  • Mark_J_Walker's avatar
    Mark_J_Walker
    Brass Contributor
    Dec 03, 2024

    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

Resources