Excel Car Cards Revisted: Adding Logo Images to the Cards

Posted on by
1

Recently I attended an operating session at my club, where we use car cards created and printed from the “ShipIt!” software package. One of the features of the ShipIt! software is the ability to include a railway logo in the top corner of the car card – which I’ve been dully aware of forever since most of our car cards have a CP Rail logo in the upper corner (since our club models CP). However, the guy that prepares and prints out the car cards has actually started using this feature and adding specific logos to cars for different railroads, and the effect is actually pretty sharp when handling the waybills during operations. Thus I got inspired to see if I could add logos to my car cards which I created in Excel. (See my previous post: Excel Car Cards and Waybills)

With a bit of googling, I found this article on ExcelGuru.ca which was exactly what I was looking for. I suggest giving it a read, as this is exactly the information I followed in order to add the logos to my car cards; although I did tweak a couple things in the middle to combine it with my data lookup functions I described in my earlier post on the subject, the original author of that article (Ken Puls) deserves a lot of credit here.

Step 1: Setting up the Image Data

The first section follows the information from the ExcelGuru article pretty much verbatim. We need a new sheet added to the workbook to contain all of the images. Each row in the image table contains two columns, one with the image name, and the second column containing the image pasted into the cell.

There are basically two ways to go about this: define an image for every railway reporting mark you use in your car cards, or just define each image by itself and add in a separate column to the data sheet to specify the image name for each car card. While the second approach causes you to duplicate some value entries in the data table that drives the car cards, it’s the route I ended up going with so that I could optionally use multiple different images against the same reporting marks in order to display lessee logos on privately owned/leased tank cars and not have to define cells for obscure one-off shortlines and companies that I can’t find logos for.

Note that whatever route you go, every possible value that the data could use *must* be defined in the image table, or you’ll end up with the image cell on the final car card displaying a broken reference. If you define an image for each reporting mark, then each mark must be in the image data sheet, even if there’s no actual for that particular mark. There will just be a blank cell defined for that image. For defining images with names, there should be one “Blank” reference.

Each image doesn’t have to be exactly the same size, but they should all fit into the cell on the car card with at least a pixel or two of white space around the edge or it may actually cut off the card border when printed.

Once the table of images to use has been created, we turn each cell containing an image into a named reference that we can use later.

Highlight the table of cells you created, and then under the “Formulas” tab on the toolbar ribbon, in the “Defined Names” group click on “Create from Selection”. Choose to create names from values in the left column. This will use the names in column A to define a specific name for the corresponding cell in column B that can be used later.

That does it for setting up the image table, now let’s make some use of it.

Step 2: Setting up the Data Cells to drive the Image

The middle bit is where my usage may seem slightly more complicated, as in the example in the article, they link the photo to static text whereas I have yet another layer under this, using VLOOKUP functions (which I got into in detail in my previous post) to retrieve text data from another sheet. However, this really doesn’t affect the instructions much, as really the only difference instead of static text in the “driver” cell, the text is returned by that function.

However I do deviate from the article here a little, so I’ll highlight my steps here. First of course, the new data column is added to the Data sheet to specify the name of the logo image to display on the car card. I skipped creating any data validation on these cells.

Then, I added a new cell on the car card template page, just below (and outside the printed border of) the finished car card to output the image name using the standard VLOOKUP functions. I also want the cards to print properly if no logo image is specified in the data, so it’s wrapped with an IF function that returns “Blank” if no value is returned, so the picture cell displays a proper blank cell instead of “#REF” indicating a bad lookup. The final cell formula for the first car card on the sheet then looks like this, where cell E16 contains the starting car card ID:

=IF(VLOOKUP(E16,Data!A:P,16,FALSE)="", "Blank", VLOOKUP(E16,Data!A:P,16,FALSE))

Now, select that cell, and use the “Define Name” tool in the “Formulas” tab of the toolbar ribbon to manually set a defined name (e.g. “Car1Picture”) for that cell that will again be referenced later.

Next, click on the Name Manager tool in the same section of the toolbar ribbon. We want to edit the name we created and modify its reference a little. Select the new entry (e.g. “Car1Picture”) and change the “Refers To” from:

='Car Cards'!$A$13

to:

=INDIRECT('Car Cards'!$A$13)

What this does is allow this name to refer not specifically to this cell, but to use the value of this cell to refer to another cell, which will allow us to look up the correct cell in the Images table based on the changing sheet data.

Step 3: Linking and Displaying the Image

The final steps are again pretty much followed exactly as presented in the posting on ExcelGuru.

On the Images sheet, select the *cell* (not the actual image object) for the first image and press Ctrl-C to copy it.

Then on the car card template sheet, select the cell where the logo will go and paste in a picture link. It’s important to pick the correct paste option here. The ExcelGuru article shows how to do it in Excel 2010 (right click in the cell, and from the pop-up menu choose Paste Special > Picture Link (icon at bottom right like a little landscape picture with a chain link in front of it)). In the older Excel 2007, which I have, select the cell, and then under the “Home” tab of the toolbar click on Paste > As Picture > Paste Picture Link. This creates a picture object that displays a view of the linked cell.

Of course the point is for it to change based on the sheet data, and not just display the same picture, so we want to change the reference. Click on the created picture to select it, and in the formula bar change the reference to the named cell (from Step 2) that contains the image name for this car card:

And presto! The car card now has an image that will change based on the data for the car card. The final result looks something like this:

Files

If you’d like to print your own car cards, here’s an almost-blank copy of my Excel template, with just a few sample cars as data examples.

Dating via the Details: Wheels, Running Boards and Ladders

Posted on by
1

In a previous post, we looked at data, stencils and lettering that can update or firmly root a car’s appearance to a 1970s or newer time frame. Here we’ll look at some of the concurrent physical changes to freight car hardware (and rules regarding the same) that will also help indicate the modeled time frame.

Running Boards, Ladders, and Handbrakes

Model of a modern (1974-built) boxcar built with low ladders and no running boards (roofwalk). The “ribbed” outside-post construction of this car is also a post-1960s signature.

The earliest railroad boxcars featured handbrakes that were operated via means of a wheel attached to a vertically-mounted brake staff that would be operated by a brakeman walking on wooden running boards down the centre of the car’s roof.

While automatic air brake technology was introduced and improved during the late 1800s, this arrangement stuck around, with the handbrake to tie down a parked car still located at the top of the car end, and running boards (later made of steel instead of wood) to allow brakemen to move down a cut of cars to set (or release) multiple hand brakes.

The first major change in rules regarding running boards came in 1945 when wood walkways outlawed on new cars. New cars were to feature expanded metal running boards, but the wood walkways were generally not replaced on older cars. (Obviously metal running boards would have existed before this but I don’t have any “first” dates of adoption.)

The most significant change came in 1966 when the rules were changed to no longer require running boards. New cars ordered after 4/66 or delivered after 10/66 were required to be built without running boards and low-mounted handbrakes. Running boards also began to be removed from cars, with the original target date for completion being 1974, but full compliance lagged behind a bit. By 1982 walkways were banned on boxcars and reefers in interchange service in the US.

Older 1940s built boxcar that has been upgraded and modernized to remove running boards (although the former supports are very much in evidence) and cut down ladders. On this car the brake gear has also been lowered, however on many cars the brake wheel was left in its original position, with full height ladders remaining on the “B” end corners to reach the handbrake.

Journal Bearings

50 ton truck with plain bearings (left) vs modern 100 ton truck with roller bearings (right)

Railroad car axle bearings came in two distinct “flavours”. The old style featured a solid axle end which turned on brass bearing inserts inside an oil-filled journal box cast as part of the truck. This is what is known in the trade as a “plain” bearing – sometimes also referred to as “friction” bearings, although only after roller bearings became common. Modern railway axles use a self-contained, sealed package of roller bearings

Some lists of dates show the first usage of roller bearings on motor cars and some steam engines and freight cars in 1923 and first roller bearing on passenger equipment around 1926.

As far as I can tell from trolling through several lists of important dates, the first significant regulations about roller bearing journals came in the 1970s, with roller bearings required for all cars with 6 1/2″ by 11″ journals in 1972 and  roller bearings required for all cars with axle loading greater than 55,000 lbs in 1974. (Basically, larger 90-100+ ton capacity cars being built then.)

By 1991 all cars in hazardous materials service must be equipped with roller bearings and may not be equipped with plain bearings and in 1995 plain bearings were banned from interchange entirely.

Trucks

The earliest freight car trucks were wood beam trucks with a heavy wood beam upper member and cast iron or steel journal boxes, spring hangers, bracing and other hardware. By 1870 “archbar” truck frames fashioned from heavy steel bar bolted together started replacing wood beam trucks as standard. Later trucks used various types of cast sideframes. Archbar trucks were banned from interchange service after July 1, 1940.

By 1958 all trucks applied to new cars were required to have AAR approved side frames with U-shaped castings and integral journal boxes. Cast truck side frames with T, I, or L shaped cross sections were prohibited under cars in interchange service.

Wheels

The last few changes are harder to note quickly from a distance as they’re underneath the car, but can still be seen in a side view.

Ribbed-back cast iron wheels (left) vs steel wheels (right)

Modelers may have noted that there are generally two styles of wheels available with some model trucks available (either as replacement trucks or included in some full kits or RTR models). Some wheels have a fancy-looking ribbed back appearance, while others have a profiled but plain back face such as the truck at top right in the photo. (Of course, some model wheelsets also have a completely smooth undetailed [inaccurate] flat-backed design….)

The ribbed back wheels represent older cast iron (not steel) wheels. Cast iron wheels were hardened in chilled water. To withstand this process the had ribbed backs. Steel wheels did not require or have these ribs.

As of January 1st, 1958 cast iron wheels were banned from new and rebuilt cars,  From January 1st, 1964 no new cast iron wheels were allowed on existing cars, and from January 1st, 1968 all cast iron wheels were banned from interchange.

This is also a good place however to mention different sizes of wheels. Generally speaking, most older freight cars had used 33″ diameter wheels. However cars of 100 tons capacity or above rode on larger 36″ diameter wheels. Many modern trilevel autorack cars ride on low profile 28″ wheels. If you’re replacing wheels on your model freight cars and aren’t quite sure whether you should be using 33″ or 36″ wheels, take a look at the CAPY or LD LMT data lines below the car number. If those numbers are around 200,000 (lbs) or above, use 36″ wheels. Around 150,000 (lbs) is a 70-ton car likely with 33″ wheels.

Brake Systems

KC brake piping diagram

The first Westinghouse air brakes were patented in 1869.

In the early part of the 20th century, the common air brake systems in use on freight equipment (known as K or KC brakes) were a simplified affair where the brake cylinder, air reservoir and control valve were all combined as one unit. The more modern air brake system design (known as AB brake systems) that most people will be more familiar with are quite visibly different, with separate components for the brake piston, control valve, and a large air reservoir with separate halves for service [regular] and emergency brake applications.

In 1933 AB brakes were required on all new cars and the older K/KC brake systems were outlawed from interchange service in 1953.

AB brake piping diagram

Wawa Station Roof Profiling

Posted on by
3

I’ve been taking advantage of free time over the long weekend (“Family Day” civic holiday in Ontario today) to tackle the next major construction piece of the project – the “flat” roof.

In actuality, the roof is not quite flat in order to allow rainwater to properly run and drain and not build up on the roof and cause rot, leaks or other problems.

The architectural blueprints show how the roof slopes away from the edges to drains. On the upper roof over the second story a 2×6 board on edge establishes the height of the roof at the edge of the roof, sloping down to “zero” at the central drain. Construction details are: 7/8″ sheathing flat on the second story roof/ceiling joists, 2×6 support at perimeter (with successively cut down supports to allow roof to slope), 7/8″ top sheathing and finished with a tar and gravel surface.

This first photo (above) shows the styrene 2×6 strip added all the way around the perimeter of the roof. The second story roof was actually divided into two drainage areas, so an additional pair of support 2×6 strips run down the centre of the roof dividing it into half.

Roof sections made of .020″ thick styrene sheet being added.

Short pieces of .020, .040 and .060 styrene strip help support and reinforce the joints between roof sheets.

Roof sheets completed, and joints touched up with spot putty. The slope effect ends up actually being pretty subtle overall, but is obvious in the indented corner on the second story, as well as over the passenger waiting room wing where the drain is at the edge of the roof and everything slopes down to this point. On the large open area of the baggage room where the entire roof slopes to a central point, the effect is difficult to see without laying a straightedge on the wall caps, but the modeler knows it’s there!

The final finish work to the roof will involve a bit of trimming around the chimney and wall caps, and the final surface will be a representation of the bonded tar-and-gravel surface of the prototype. This however, will likely be one of the last steps after painting the rest of the structure.

Wawa Station Brickwork

Posted on by
Reply

Construction progress on the Wawa station continues to move forward. A major (but relatively simple) step in the progress is applying the brick texture to the lower levels of the structure, as well as constructing the brick chimney.

In these first two photos, we see the brick sheet in progress of being added to the lower story of the structure. I had some sheets of Plastruct HO brick that I’ve been using on this station; similar brick sheet products are available from JTT, N Scale Architect and other sources.

The brick sheet is cut to size, window and door openings roughly cut out and glued to the structure core using liquid plastic cement. Then the windows and door openings carefully cleaned up with needle files.

Fashioning the chimney is slightly more interesting, as the core is build up from various “4x” (4×12, 4×8 and 4×6) styrene strips and then laminated with the Plastruct brick texture sheet.

Wawa Station Trimwork

Posted on by
Reply

The Wawa station build keeps moving forward with the detailing of the structure.

The station featured these decorative eaves around the entire structure below the tops of the walls. To represent the proper shape of the eaves, I built them up out of styrene strip. The core is a 4×12 strip on edge forming the base of the protruding eave, with a 1×12 laminated on top to achieve a proper thickness and cover the joints between the larger strip.

To represent the decorative edging, fascia strips of 1×6 and 1×3 are added around the outside edge after the main core is built up.

Starting to look a bit more like the drawings and photos, but still much more to do…