You are here:

Excel/Personal Business Inventory Macros



I am trying to compile a spreadsheet for a friend who is building a Mary Kay Inventory.  She is relatively Excel illiterate while I am fairly proficient.  I will be using the newest versions of Excel (07-10)  I, however, have little/no experience in creating macros or using VBA.  I would like to create fields to where she can enter a number in "Items Sold" and that number would automatically subtract from the column "Items in Inventory".  There should also be an input field for "New Stock" so that any purchases that are made into inventory are automatically added to "Items in Inventory".  I will also have conditional formatting so that once the "Items in Inventory" column becomes less than the "Restock Item", it will notify the user that the inventory is low.

I know this must be relatively simple, but again I need an explanation in layman's terms for a first timer.  



If I was going to do this for myself, I would have a single sheet and I would enter each transaction on a separate row.  Stocking would have a postive value and Sales would have a negative value in the quantity column.  

On another sheet you could have the current balance using the new Sumifs formula which allow summing on multiple conditions.  You could then have the restock quantity below each item and if you want to enhance the visibility or need to take action, do conditional formatting.  

Creating some type of input form just seems to make things more complex.  In the data sheet, you would just be typing things in like you would in word except you would have to move to the next cell and for a new transaction, to the next row.  It wouldn't take long to develop those skills and she would have an audit trail where she can see all of here transactions.  You can put a pivot table against this data so your can analyze the heck out of it with simple commands.  

With macros, things get very complex.  You have to anticipate every mistake the user might make and account for it.  

with the data approach I first mentioned, you could have a key field and have most of the descriptive data including price and so forth pulled in from another sheet using one of the lookup formulas or a combination of index and match.  Like a macro, these would already be set up and may only need to be filled down periodically.  This would require a produce sheet where you had the data on the products involved.  She might need to copy here data and do a paste special values on old data if she doesn't want it to update when she edits product sheet data.

If you still want to go the macro route, I can give you some basic macro examples if you send a sheet that is already set up so I know what the macros have to do (and include a description of what you want it to do).  But I think this would just turn into a nightmare if you are not a proficient coder because I am sure the user will want to change things or something won't be anticipated and you will have to maintain the code.

You can send to

Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2017 All rights reserved.