SOLVED

A plea for help - Excel formatting

Copper Contributor

Hi there!

I'm working as a security guard at a postal service, and I write down every detail of a truck that comes to our depot. This being their route, what they carry, their license plate and where they need to dock. I have been doing this on pen & paper because that's how it was done before my time, but it's too time consuming for the influx of trucks that I get per day. Normally it wouldn't be a problem, but I have different trucks that need to have different data be put into them, so it would naturally require different papers/tables.

So I wanted to make a spreadsheet where I could have a main spreadsheet, where I input all the data, and have it be automatically be put in a separate spreadsheet in a separate table, so I don't need to keep swapping papers and be buried in a mess of A4's. I have attached a crudely drawn idea.

I would essentially put information in different tables at the main (like in the drawing), and it would automatically put them into the different tables and add rows according to how much data I put in. 

Thank you very much in advance if you choose to help!

6 Replies
best response confirmed by ThomasMPrins (Copper Contributor)
Solution

@ThomasMPrins 

 

I'd be willing to try here. But we'd need a more complete description. Is it possible for you to make some photocopies of the sheets of paper you're keeping now, clear enough so that I can see what the pieces of information are (column headings, once they get to Excel), but crossing out actual license plates and any other info that should be kept confidential? It's really important that we design a database that is set up to capture the actual info....so just having ABC and DEF, etc., gives the rough idea, but not enough to work with.

 

Tell me, too, what you'd be putting in separate spreadsheets....are those primarily summary reports? Or are they different extracts for different companies, different types of routes, ... what?

 

In short: we need to do a good definition of the project. You've done a reasonable overview, but it'll need to get more specific. And a copy of your paper records as you keep them now would be a good start.

Hello @mathetes !

Thank you very much for your reply. Attached I have added all different tables I use at this moment. Everything provided by these sheets of paper need to go to other coworkers, so that's why I have to divide each truck into their right table. 

What I'd put into different spreadsheets are the tables you see in the attached image but in full, one which adds lines with every truck that comes along. While in the main the tables would only serve as an input rather than an actual functioning table.

@ThomasMPrins 

 

Can you describe

  • your own level of comfort and experience with Excel [the fact that you're describing this as a data table tells me at the very least that you're quite logical, so should be quick to learn even if not all that experienced yet; also that you are differentiating between the INPUT and the OUTPUT stage of things)
  • what version of Excel you and your colleagues have (the reason for asking this: the very newest Excel has some functions that would make it very easy to create those secondary reports by truck, especially if you can just do them as PDF documents to be sent out)

 

I'll create a model in Excel based on these most recent images....but be assured it's going to take multiple iterations before we settle on something that really works.

@mathetes 

I would think of myself as a new student of excel haha, I haven't had much experience with excel before coming across this job. I used to take computer science in high school, so that's probably where I get all the logic from.

Currently I'm not at work so I won't be able to check but I would say its the 2016 version. I'll be able to check properly on Monday.

Thank you very much for helping! I'll definitely try to immerse myself more in the world of Excel so I can be of more help.

@ThomasMPrins 

 

I"m attaching what is meant solely as a rough first draft of a main Input sheet. "Rough" because I need you to play around with entering some kind of representative data into it. There are several columns there that I copied from your diagram, but which have no meaning to me... (RC, EURO, WWP, EPK).

 

Once you've entered some representative data, re-post it and describe what you'd like in the way of reports from that data that's been entered.

 

I also find myself wondering what the secondary or report sheets represent. So perhaps you could clarify that. Are they meant to represent companies? Truck companies? Is that first column in the two examples--where one of them reads "Oegema Pendel" and the next "Aarts Pendel" what would go in under "Customer" in the main sheet? If so, then there should be a field added to the main Input sheet to capture that information, because that will be a key to extracting the information for each Output report.

 

You'll notice I added "Route" to the main sheet. There generally can't be any columns in the output sheets that aren't represented in the Input sheet in some form or other. They might change (through computation of some kind), but at the very least I need to understand the relationship between the Input and the Output, where things are coming from.

 

 

Hello @mathetes,

I have e-mailed you my response.

Thank you very much,
Thomas Prins

1 best response

Accepted Solutions
best response confirmed by ThomasMPrins (Copper Contributor)
Solution

@ThomasMPrins 

 

I'd be willing to try here. But we'd need a more complete description. Is it possible for you to make some photocopies of the sheets of paper you're keeping now, clear enough so that I can see what the pieces of information are (column headings, once they get to Excel), but crossing out actual license plates and any other info that should be kept confidential? It's really important that we design a database that is set up to capture the actual info....so just having ABC and DEF, etc., gives the rough idea, but not enough to work with.

 

Tell me, too, what you'd be putting in separate spreadsheets....are those primarily summary reports? Or are they different extracts for different companies, different types of routes, ... what?

 

In short: we need to do a good definition of the project. You've done a reasonable overview, but it'll need to get more specific. And a copy of your paper records as you keep them now would be a good start.

View solution in original post