Using Excel for Inventory

I store my cars in cases, sorted by type of car. In Excel, I have a separate worksheet for each case of cars. I also have a master sheet that contains the inventory of all cars in all cases. The master sheet allows me to sort by mfg, roadname, etc.

 

Gilly

Gilly

 

Are you entering each item twice (once in "Case worksheet" and once in the "master sheet")? 

 

Thanks

Bill

I use Apple's Numbers program, but it works similarly as Excel.

 

Newer versions of Excel have "Format as Table" in the "Styles" section of the "Home" tab in the ribbon.  This is what I use.  I use a row per item and the columns for what info I want to keep for each car.  So row one will be the column titles and when you select "Format as Table" be sure to check the table has headers option.  Now you can sort the table by any of the columns as well as select what is or isn't shown.  My columns are Gauge/Type/Sub Type/Road/Road Short/Road Number/Color/Has Box/Ready to Run/Brand/Catalog Number/Photo/Notes.  So a couple of examples wold be:

 

O/Steam Locomotive/4-8-8-4 Big Boy/Union Pacific/UP/4015/Black/Yes/Yes/K-Line/K3790-4015S//

 

HO/Freight Car/Hopper/Santa Fe/ATSF/49863/Red/No/Yes/Tyco///Has coal load.

 

In this way I have one sheet with everything in it, and by using the selection feature of the table's column drop down menus I can display whatever I want.  I can sort by gauge, show only passenger cars, list all green items, or about any combination I want.  Unlike Gilly, I don't have any kind of location to worry about, like what box each item is in, but if I get to that point, a simple addition of another column and now that will be ready to go.

Bill,

 

I use Excel for inventory purposes.  I have two separate spreadsheets (never took the time to combine them) that have slightly differing purposes.

 

My Rolling Stock #nn.xls is a spreadsheet with tabs for Rolling Stock, Trains, Buildings, Electronics, Vehicles, Figures and whatnot.  Each tab contains item descriptions, indicates where and when it was purchased, price, and storage box number.  This comes with me to York to prevent duplicate purchases.

 

MRR Storage.xls is a spreadsheet with numbered tabs indicating storage box (plastic bins) numbers.  Each tab's sheet shows the item and quantity.  This is how I keep accurate track of where an item is stored.  I can show a piece of rolling stock within the bin or indicate that the box is empty (it's on the layout).  Typically, bins hold cars by type.  Other bins hold structure kits, detail parts, automobiles, electrical components, etc.

 

In addition to these, other storage bins are labeled as project boxes.  These hold the materials needed to construct a particular project or kit.  If the material has gone to a project bin, it is removed from the MRR Storage spreadsheet.

 

To answer your questions:

1) Data is in multiple worksheets

2) No tools, reports, or special functions are used to create different views.  No macros or code has been written.

3) No pictures have been used.

 

George

I have an excel database of the locos I own, but not the rolling stock.  I've been remiss though - have not updated it in a while.  I need to do that.

I do use Data filters a lot, that is a handy tool that does not require programming knowledge. 

 

For example, if you include columns for parameters such as manufacturer, freight/passenger, Steam/Diesel, etc., you can use filters to then only display types of items in the larger list that you wish to see.  (i.e., show me all my MTH items, or show me all my steam engines, or show me all my freight cars)

 

-Dave

Excel or most any spreadsheet makes a fine "flat file data base."

 

I add columns anytime creating additional info.

 

First Sheet is for locos and rolling stock.  Second Sheet structures and buildings.  Third Sheet for figures.  Plus a Sheet of die-cast vehicles.  And also a Sheet of my RR related Library.

 

Can sort any way from Sunday.

 

Before creating this data I would come from a show finding I already had something purchased at the show.

 

Also each year I add a "Wish List" down from the end of the Loco/Rolling Stock Sheet.  Before making a new years Wish List I transform the existing Wish List into the Purchase List for that year.

 

Works great, see no need for a relational data base for this use!

 

Have to agree.  Using a relational database for this type of effort is like swatting a fly with F-16.

 

George

 

 

Or killing a mosquito using a bazooka.

 

I have been using Excel for a while now as well.  I have one work sheet per major vendor and one miscellaneous sheet.  Simple to set up, make additions and maintain.

Bill,

 

I use Excel. I have a worksheet for each major end item - locomotives, box cars, Reefers, ...ect. Each worksheet list the item road name, item catalog number, road number, manufacturer and a subjective value, which is usually the purchase price. The total value for each sheet is calculated each time there is a change. My son also set up a function that calculates the total value of my trains. It is a nice to know number if you ever had to replace them. It is also a number that after 50 years of buying trains will scare you!

 

I enter on the "Case" worksheet and then copy/paste to the master sheet. Since the master sheet gets sorted, I just tack new entries onto the first available row.

 

Gilly

You could just have a separate column for "case" and then just generate reports based on either the master or case data.  That gives you only one copy of the data and kills both birds with one stone.  

 

 I use excel.  One worksheet for everything.  I can then sort and/or filter however I want.  I can also easily use the search function to find anything by item number, description, roadname, etc.

I don't think I saw this yet....I have a column in which I have a hyperlink to a picture of each item.  I try to take a picture of every item as I receive it.  I store the pictures in a folder on my laptop and then imbed a hyperlink to the picture for each row/item.  The link looks something like this:  personal\trainpics\52168.jpg  When I click on the hyperlink within that cell, it opens the picture.

 

Now, I just need to figure out how to make the spreadsheet and all the pictures available on my iPad....for more mobile access.

I have used my own Excel sheet since I started collecting trains.

 

Here is a copy of the front summary page.

 

<colgroup><col span="3" width="64" /><col width="77" /><col width="64" /><col width="75" /><col width="82" /></colgroup>

CATEGORY					Total	Total Val
					No Pieces	USD
1	Steam Engines				1	$450.00
2	Diesel Engines				1	$205.00
2a	Subway & Trolley				1	$205.00
			Sub Total Engines:	3
3	Passenger Sets				8	$775.00
4	Freight Sets				6	$230.00
5	Lionel Cars				0	$0.00
6	MTH Cars				0	$0.00
7	Weaver Cars				0	$0.00
8	Other Cars				0	$0.00
			Sub Total Cars:	14
9	Electrical Accessories				2	$500.00
10	Signals & Controllers				5	$125.00
11	Buildings				1	$40.00
12	Bridges and Misc Accessories				1	$15.00
13a	Trackage				0	$0.00
13b	Other Track				0	$0.00
14	For Sale				0	$0.00
15	Wanted
				TOTALS	26	$2,545.00

 

Each category has its own sub-sheet or tab where you enter details.

Then the item count and totals are pulled to this summary sheet automatically.

 

The last time this topic came up I offered my sheet to anyone who wanted it, and several took me up on the offer. They all seem to be quite happy.

I'll repeat that offer here; anyone who would like a copy send me an email, and I will oblige and send you a mostly blank copy. A few example items are listed just for illustration. You can overwrite them with your own items.

There is also a tab of user notes just for illustration.

 

Regards,

Rod

I... Have a drawer.

My inventory is a flat spreadsheet.  The sort command produces any report that I need.  I works just fine for a few hundred items.

 

Jan

I have been using Excel for a number of years now. I set mine up to have columns which keep certain information for each item.

 

The columns that I use are.

1- Overall Item #

2- Kind Item #        A number for each item for each Category like Steam, Electric  

3- Make          Lionel, MTH, K-Line, etc. - each Mfg. items are kept together

4- Model #          Mfg. Item # in sequence

5- Description

6- Set #          My own set number for each item of a set

7- Year Made

8- Scale          a X in a box if it is a scale item or not.

9- Features          Legacy, TMCC, etc.

10- Value

 

I then have the following Categories starting from the Top down.

Scale Steam Locomotives

Steam Locomotives

Scale Electric Locomotives

Electric Locomotives

Scale Diesel Locomotives

Diesel Locomotives

Scale Motorized Units

Motorized Units

 

Then after the motorized pieces I have my car Categories with each section like the motorized units as Scale & non-scale as the engines are listed above.

like Scale Passenger Cars

             Passenger Cars for each of the following

Box Cars

Operating Cars

Cabooses

Special Cars

Hoppers

Gondolas

Flat Cars

Tank Cars

Stock Cars

 

Followed by the following Categories

Operating Accessories

Accessories

Track & Transformers

 

After each Category I leave 2 blank lines between the last item of a category and the Category Title. To the right of each value is a master value of each Category. In the near future I may divide the Accessory Categories up.

 

The way I have it set up I can print out a master listing on paper in the landscape mode.

 

Here is one of mine, not up to date but close.

It is do with it whatever you want style.

To print, make your printer 'fit to page' mode.

File attached