Computerised Switching

The switcher spreadsheet automates the generation of switching moves for your model railroad based on the information you supply about freight cars, the loads they carry and the destinations assigned to them. The moves can be printed out in switch list format or in car card / waybill style.

Two different switchlist formats are possible, one suited to landscape and one to portrait printing.

Landscape:

Portrait:

Car card/waybill style:

Each car, load and location is given a name, plus an id number for cross-linking. The computer then takes over and generates a sequence of switching moves spread over a number of trips.

Cars are assumed to originate from and return to one or more off-scene fiddle yards representing other locations.

As supplied, the spreadsheet contains three alternative sets of data for switching industries on the layout shown in the diagram below. You will need to overwrite this data with data appropriate for your layout, though you don’t have to use all three sets. A copy of the data in the first sheet can also be found on the Example sheet, so if you go wrong you can always copy this back and start again.

Layout diagram courtesy of ‘Continental Modeller’

1. Locations

Columns BG and BH on the datasheets contain the id and name of each spot location. Only one car can be spotted at any one location at a time (unless you’re allocating a block of cars as detailed in section 2), so for an industry with more than one spot location, simply enter more than one location id for that industry, with an appropriate name (or even the same name) for each.

Only enter the location name for a particular id once.

Column BK contains the ids of the loads associated with each location.

Column BL contains the number of times each load will be spotted per sequence. Fractions are catered for to vary the probability of some or all of your loads being delivered in a given sequence. For example, 0.5 means there will be a 50-50 chance the load will be dealt with, 0.8 will give the load an 80 percent chance of being delivered, and 2.5 will mean there will be either two or three deliveries in the sequence.

The same load id can be assigned to more than one location.

The trips for each location will be ordered randomly before the sequence starts, then cycled through in the order generated.

Loads cannot be allocated for delivery on specific days.

Optionally, a direction (1 or 0) for each location can be specified in column BI, for use with the “Split directions” option on the main sheet. In this mode, each location is visited on alternate trips only.

Column BJ can be used to ensure a spot or pickup is not blocked by a car at an adjoining location. Entering the id of the “blocking” location will ensure that a car at that location is removed to enable the original spot or pickup to take place. If split directions are used, and the locations are approached on different trips, this option will have no effect.

In the example, location 1 (Shaw’s Warehousing and Storage) has three assignments of load 4 (dry goods), one of load 7 (perishables) and one of load 12 (chilled goods), for delivery during the switching sequence. Both Wilkinson Printers and Sunderland Engineering West receive load 11, fuel oil, on average once every other sequence.

Pagano and Sunderland East are both on the same spur as other locations, which are specified in column BJ to ensure they are freed when Pagano or Sunderland East need to be spotted.

If you decide at a later stage to introduce a new load to a location, don’t insert a new row into the spreadsheet as this will interfere with the rest of the data. To save having to shuffle the data about too much, you can over-compensate by leaving more spaces than you need for each location, and entering zero in the load column.

2. Cars

Columns Z and AA contain the id and description of each car. The car list must start in cell Z4 and the cars must be numbered sequentially from 1 with no gaps.

The car description column can contain any data you wish to identify the car by. Typically, you’ll have more than one car for the same railroad company, so you’ll probably want to include its number. Or the descriptions can be duplicated to keep them generic, as with cars 4 and 5 in the example.

It’s not possible to assign cars to a block for moving around together, but you can fudge this by assuming that a particular car id refers to a block and giving it a suitable description, e.g. “4 hoppers”.

Up to 1000 cars can be specified.

3. Loads

Columns B and C on the Data worksheet contain the id and name of each load. The load list must start in cell B4 and the loads must be numbered sequentially from 1 with no gaps.

Column D specifies the direction of the load as seen from the perspective of the industry. Letter o (not number zero) means outbound, i.e. the car is spotted empty and returns loaded, and i means inbound, i.e. the car is spotted loaded and returns from its spot location empty. If a direction is not specified it will be ignored.

The direction column can also be used to ‘force’ a car from one location to another, where a load needs to be transferred between them. If the number of the second location is entered into this column, as long as the first location has had the load in question assigned to it somewhere in columns BK to BT, the car will be spotted empty at the first location, then ‘loaded’ and moved to the second in successive spots. This type of operation won’t work for a chain of more than two spots.

Instead of o for outbound, letters f for full or l for loaded (not to be confused with number 1) can be specified if preferred, and instead of i for inbound, e for empty can be specified. All letters can be in either upper or lower case.

Columns E to X contain the ids of the possible cars that can be allocated to each load type (columns J to X are hidden but can be unhidden if needed). The cars are defined elsewhere on the worksheet and, if there is more than one per load, they will be ordered randomly before the sequence starts, then cycled through in the order generated.

By specifying more than one car per load, a load can be delivered to different locations in the same trip using different cars.

Looking at columns B to F in the example, load 1, olive oil & deli, is inbound (direction i) and can have cars 4 or 9 allocated to it.

Looking further down, load 11, fuel oil, has been allocated just the one car, 10, which we will specify as a tank car. This load has no direction specified as the assumption is that it will be part full at any given time.

The load descriptions can be duplicated where this makes sense, for example, where a particular load can be either outbound or inbound depending on its spot location.

A car can be allocated to more than one load, so for example, you could use the same boxcar for both dry goods and olive oil & deli on separate trips. In each case the load can be either inbound or outbound. A car carrying an inbound load to one location may then be used to remove a load from the same location, or be transferred empty to a second location requiring an empty car.

Up to 1000 loads can be specified.

4. Locos (optional)

Columns BV and BW can optionally contain the id and name/number of the locos to be used. The loco list must start in cell BV4 and the loco ids must be numbered sequentially from 1 with no gaps. Each trip a loco will be chosen at random from the list.

If you enter a loco list but choose not to use it, you can suppress the loco display on the main screen (see below).

5. Offscene locations (optional)

For each load, a notional offscene supplier and customer can be specified. The load ids should be duplicated sequentially in column BY for ease of reference, and any descriptions entered into columns BZ and CA are assumed to be the names of the source and destination for the relevant load or empty car, in row order. In many cases they may be the same. They will be shown in columns F and I of the switch list respectively, depending on whether a car is being spotted or returned.

6. Generating the sequence

Once all the data has been entered, click the New Sequence button on the Switch List worksheet to generate the moves. Each click of the New Sequence button will produce a new list of moves based on the current operating and display options.

To start afresh with a blank sheet, for example after you’ve changed or added new data, or moved any of the datasheets around, click the Clear List button.

The moves can be printed in list format or in car card/waybill style for cutting out. The car cards will be presented in print preview mode and also stored on the Car Cards sheet. The page format of this sheet should be set to letter or A4. The car cards are colour-coded depending on whether the move is a spot, a pickup or both, i.e. a transfer between locations.

Data sheets

The program provides three alternative data sheets, to allow for, say, running stock for different time periods, or for use on different layouts. Simply choose which one you want to use by clicking the appropriate option button. As supplied, the data sheets are called 1, 2 and 3, but you can give them any name you like. However, they must be the second, third and fourth sheets in order, immediately to the right of the Switch List sheet.

Direction options

There are three options for travel direction.

Same direction each trip: typically for single track layouts with one staging yard which the trains originate from and return to.

Reverse direction each trip: for use where all locations are visited both ways, e.g. on a layout with a fiddle yard at both ends.

Split directions: where some locations are spotted from one direction and others from the opposite direction, e.g. for industries on opposing spurs, or either side of a double track. This option uses the direction indicators in column BI. Direction must be specified as either 1 or 0. If you find cars are being spotted in the wrong direction, simply flip the numbers round.

The direction indicators are ignored for the first two options.

Continuity

Some cars may be left spotted at the end of a sequence and, unless the checkbox entitled “Clear all locations at end of each sequence” is ticked, they will start the next sequence from the same spots. To ensure a carry-over from the latest sequence next time you open the spreadsheet, it must be saved before closing.

Details of cars left spotted at the end of the previous sequence are stored on the “Starting Spots” sheet.

If the ‘Show occupied locations at start of sequence’ box is checked, cars left spotted at the end of the previous sequence will be listed at the top of the next sequence under “Occupied locations”.

Condensed view

If the ‘Show condensed view’ box is checked, spots and pickups will be listed in the same column and there will be only one column for each of car, load and from/to. This format is more suitable for portrait printing.

Hiding unwanted columns

The ‘Show from and to’ locations box can be unchecked to hide columns F and I (or column G in condensed view – see below).

If the “Show locos” checkbox is cleared, column B will be hidden from view.

Off-spots

If the “Use off-spots” box is checked, occasionally the switch list will bring a car onto the layout before its destination spot has been vacated. In this case the spot location will read “Off-spot” and the car at the final destination will show on the next line as being held. The move will be completed next trip.

Summary of manoeuvres

1. To one spot location and back again (column D load direction = i or o). Loaded cars are unloaded at destination and vice versa.

2. To one or more spot locations before returning. Load direction can be unspecified. If load direction is specified, loaded status must alternate with empty, e.g. a car starting off loaded will be emptied at first location, loaded at second location etc. Car may be returned to storage in between.

3. To paired locations (load direction = number of second location). Car is spotted empty at first location, loaded and moved to second location before returning.

4. Randomly, to off-spots where destination still occupied (if option chosen). Car is moved to destination next trip.

7. Validation

Before the switching moves are generated, columns B, Z, BG and BU will be checked to ensure they contain data. If any of these columns are all blank, or their first cell (row 4)  is blank, you will be shown a message. Otherwise it’s up to you to make sure your data makes sense.

It’s not strictly true that cars, loads, locations and locos must be numbered sequentially from 1. As long as they are marked in some way (non-blank) in their respective ID columns, the program will recognise them. Without sequential numbers, though, you might get a bit confused, because items are cross-referenced by using their numerical order in the lists. So even if, say, a load is marked with an x, or given the number 10, if it is second in the list, it will be assumed to have an id of 2.

You’ll notice a sheet called Debugging. I use it for tracing bugs: if you find any, let me know.

8. Copyright

You are free to use the spreadsheet and to modify it to suit your personal needs as long as you leave my copyright notice in the program code and acknowledge me as joint author.

My permission must be sought in the unlikely event that you wish to use the program or a modified version of it for commercial gain.

If you have any queries on operating the spreadsheet, feel free to contact me at rod_shaw@tiscali.co.uk

Rod Shaw, 2017

visitors since 29/07/2014