Using the Macro: The Categories Database

Once you enter a recipe, chances are you may need to add some ingredients to the 'Categories' tab.

Adding an Ingredient

You will know you need to enter a new ingredient because it will show up in the 'Uncategorized'  column of the shopping list.

You will notice that there are no quantities listed in this column.  This makes it easier to enter the whole list into the 'Categories' tab, but it does mean that you will need to enter these ingredients before you can shop.  First you'll want check for misspellings in the 'Uncategorized' list as a misspelled ingredient will get it sent there.  Once you're sure everything is spelled correctly, copy the whole list and paste it in the empty rows at the bottom of the list of ingredients in the 'Categories' tab.

In column B enter the unit you want to use for your shopping list.The units you can enter are cups, tbsp, tsp, lbs, oz, gallons, each, bundle, cans, cloves or inches. 

In column C enter the category.  Your options are Meat, Dairy, Canned, Produce, Baking, Frozen, Other or Staples.

Now you need enter the conversion multipliers.  The conversion multipliers are what the program multiples the quantity of an ingredient by to convert it to the shopping list unit in column B.  For example, the shopping list unit for vegetable oil is oz.  If a recipe calls for 1 cup of vegetable oil, that needs to be converted to oz.  There are 8 oz of vegetable oil in a cup, so the 'cups' column (column D) is 8.  The one cup gets multiplied by 8 and the macro records 8 oz of vegetable oil to the shopping list.

Liquids are easy since the conversion from volume to weight is constant.  Solids are harder and involve some guess work. I've created formulas to do a lot of the work for you.  The tbsp, tsp, oz and gal auto-populate based on the cups and lbs.  The cups and lbs will sometimes auto-populate based on what the shopping list unit is in column B.  Look at the green columns, D and G, if they are a number other than 0, then leave them alone.  If they are 0, you will need to enter a conversion yourself.  I use Google a lot for this with searches like "how much does a cup of carrots weigh".  I update these conversion numbers periodically if I find I have too much or too little of something.  For solids like vegetables and cheeses I enter in column O what form I'm considering for the volume measurements.  i.e. a cup of zucchini is a cup of shredded zucchini.

You don't necessarily need to enter both the lbs and cups conversion multipliers.  Skip them if they don't make sense for the specific ingredient.  For example, I've never seen a recipe call for a cup of ground beef, so I left the the cups conversion as 0 since I don't think I'll ever need it.

The pink columns are optional.  For vegetables you can convert from 'each' based on the average weight of the vegetable.  The 'can' can convert from say a can of beans the shopping list unit.  The last three columns you can ignore.  They are very specific units for things like fresh ginger and garlic.

Once you've entered all the information for the new ingredients, you may want to resort the ingredients into alphabetical order.  This is not necessary for the macro but it will make it easier for you to look ingredients in the future.  To sort, drag the mouse to select all of the ingredients in column A.  Then click the 'Data' tab at the top of the window.  In the middle of the data tab will be the alphabetical sort.

When you click on the sort button, a window asking if you want to expand the selection will pop up.

Choose the 'Expand the Selection' option and hit sort.  This will make it so all the numbers get sorted along with the ingredients.

Conversion Errors

Occasionally when you run the macro you may get a red 'Conversion Error' in cell B1 in the 'Shopping List' tab.

This indicates that the macro was unable to convert units of one or more ingredients.  To see which ingredients it had trouble with, scroll over to columns Q through S in the 'Shopping List' tab.

These columns tell you which recipe, ingredient and unit had the problem.  First check to make sure the unit is spelled correctly.  If it is spelled correctly, head over to the 'Categories' tab and look up the ingredient.  The conversion error means there is a 0 in the column corresponding to the unit in column S.

Put the correct conversion number in the cell to fix the conversion error.  This error is pretty infrequent, so hopefully you will not be seeing it too often or at all.