Help with Excel Formulas
Discussion
Hi All
I need some help improving a poorly designed spreadsheet we have at work.
I will explain as best I can, but our transport department move our customer's trailers and quite often our customers will park their trailers in one of our 4 depots. The trailers are sometimes plugged in and on power (the trailers contain equipment that must generally always be turned on) and sometimes they will be parked off power.
We have to record what trailer is parked, what day it is parking on, whether it is on and off power and what depot it is parked in.
Currently we have it laid out as follows:
It's very basic - we put a '1' on days where the trailer has parked in either the on or off power lines for that trailer, and a count is tallied up at the end.
The problem with this is it does not account for the 4 different depots, so we actually have 4 different tabs in the workbook, all with 200 or so trailers, with lines for on/off power for every day of the month and a totals tab that tallies everything up.
Because of the frequency that trailers are added and removed from the fleet, the formulas are regularly broken as it's so hard to manage with so many people contributing to the same document.
While we are having a proper system built, for the next 9 months or so I want to improve this spreadsheet.
Ideally, I would like to have everything on one page, and rather than having a '1', we instead have a letter, then a formula that counts the letters (if that is even possible?) and basically distributes the counts to the correct depot. I've done a mock-up here:
So in summary, my question is basically can you add up individual letters or will sum only work on numbers?
If you can sum letters, how do I do it? How do I make each depot tab only add up the letters then represent that specific depot?
I need some help improving a poorly designed spreadsheet we have at work.
I will explain as best I can, but our transport department move our customer's trailers and quite often our customers will park their trailers in one of our 4 depots. The trailers are sometimes plugged in and on power (the trailers contain equipment that must generally always be turned on) and sometimes they will be parked off power.
We have to record what trailer is parked, what day it is parking on, whether it is on and off power and what depot it is parked in.
Currently we have it laid out as follows:
It's very basic - we put a '1' on days where the trailer has parked in either the on or off power lines for that trailer, and a count is tallied up at the end.
The problem with this is it does not account for the 4 different depots, so we actually have 4 different tabs in the workbook, all with 200 or so trailers, with lines for on/off power for every day of the month and a totals tab that tallies everything up.
Because of the frequency that trailers are added and removed from the fleet, the formulas are regularly broken as it's so hard to manage with so many people contributing to the same document.
While we are having a proper system built, for the next 9 months or so I want to improve this spreadsheet.
Ideally, I would like to have everything on one page, and rather than having a '1', we instead have a letter, then a formula that counts the letters (if that is even possible?) and basically distributes the counts to the correct depot. I've done a mock-up here:
So in summary, my question is basically can you add up individual letters or will sum only work on numbers?
If you can sum letters, how do I do it? How do I make each depot tab only add up the letters then represent that specific depot?
Don't feel bad, there are about a billion formulas in Excel & it's often difficult to know which one will do what you want. There are some very advanced users on here (not me) & they can usually come up with th e answer if they have all the information presented clearly, which you did.
If the spreadsheet is going to be accessed by several users, updating the wherabouts of 200 trailers several times every week, there will almost certainly be input errors to deal with over 9 months.
To reduce (if not completely exclude) those, perhaps:
Use a Named Range for the eight possible parking options (L - Power, L - No Power, B - Power, etc)
Remove the alternate "OFF" rows
Use Data > Validation so that you can only use the list above as a drop-down for each cell
Using SumProduct formulae, or a Pivot elsewhere, you can then calculate number of trailers powered at each site each night etc per your reporting requirments.
Unlock the Input cells and Lock the formula cells to avoid fat-finger errors and accidental formula overwrites / breakages.
As always with Excel, spend a little time setting it up once, and save a load of time using it and improve the quality of the info.
To reduce (if not completely exclude) those, perhaps:
Use a Named Range for the eight possible parking options (L - Power, L - No Power, B - Power, etc)
Remove the alternate "OFF" rows
Use Data > Validation so that you can only use the list above as a drop-down for each cell
Using SumProduct formulae, or a Pivot elsewhere, you can then calculate number of trailers powered at each site each night etc per your reporting requirments.
Unlock the Input cells and Lock the formula cells to avoid fat-finger errors and accidental formula overwrites / breakages.
As always with Excel, spend a little time setting it up once, and save a load of time using it and improve the quality of the info.
sideways sid said:
If the spreadsheet is going to be accessed by several users, updating the wherabouts of 200 trailers several times every week, there will almost certainly be input errors to deal with over 9 months.
To reduce (if not completely exclude) those, perhaps:
Use a Named Range for the eight possible parking options (L - Power, L - No Power, B - Power, etc)
Remove the alternate "OFF" rows
Use Data > Validation so that you can only use the list above as a drop-down for each cell
Using SumProduct formulae, or a Pivot elsewhere, you can then calculate number of trailers powered at each site each night etc per your reporting requirments.
Unlock the Input cells and Lock the formula cells to avoid fat-finger errors and accidental formula overwrites / breakages.
As always with Excel, spend a little time setting it up once, and save a load of time using it and improve the quality of the info.
Great advice there.To reduce (if not completely exclude) those, perhaps:
Use a Named Range for the eight possible parking options (L - Power, L - No Power, B - Power, etc)
Remove the alternate "OFF" rows
Use Data > Validation so that you can only use the list above as a drop-down for each cell
Using SumProduct formulae, or a Pivot elsewhere, you can then calculate number of trailers powered at each site each night etc per your reporting requirments.
Unlock the Input cells and Lock the formula cells to avoid fat-finger errors and accidental formula overwrites / breakages.
As always with Excel, spend a little time setting it up once, and save a load of time using it and improve the quality of the info.
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff