To help with the learning process I have added videos to show the steps involved.

user defined function excel not updating-55

But the most important feature is that we can filter all this information over time and check the stock levels of all stock.

The template for order and inventory management is available for download below and all of the code will be included in the article.

(Note: I abhor many, many things about Excel’s default settings, but, to keep the example as familiar as possible, I’m going to leave those alone.

This basic approach is one of the core components in the dashboards I work on every day, and it can be applied to a much more robust visualization of data than is represented here.

We’ll name the cell in the first row of each metric column as the current value for that metric (the cells don’t to be named cells, but it makes for easier, safer updating of the dashboard as the complexity grows).

Name each cell by clicking on the cell, then clicking in the cell address at the top left and typing in the cell name.Create these static named range and the dynamic named range below. $C:$C000)) Data validation for Products dependant list =OFFSET(Cascade, MATCH($B, Cat_Validation,0),0, COUNTIF(Cat_Validation,$B),1) For the data validation to check “Out of stock” “Reorder now” go to the data validation and choose List and in the Source type the text above. Advanced Filter Action:=xl Filter Copy, _ Criteria Range:=Sheet1. I’m sure you would agree there has been a lot to learn here about application development and using code to transfer data from user forms to data sets.Add data validation to the category drop-down list and use the dynamic named range “Category” Add data validation to the products drop-down list and use the formula that you see below on the worksheet for the source. $D Dynamic named range Cat_Validation =OFFSET(Products! Please note that this code calls to macros called Unprotect_all and Protect_all '————————————————————————————— ' Procedure : Adv Stock ' Author : Trevor ' Date : 22/06/2013 ' Purpose : '————————————————————————————— ' Sub Adv Stock() On Error Go To Adv Stock_Error Application. Please let me know your thoughts as to how you got on and any suggestions you might have.It’s important to have consistent naming conventions, so we’ll go with _Current for this (it works out to have the metric identified first, with the qualifier/type after — just trust me! The screen capture below shows this being done for the cell where the current value for Orders will go, but this needs to be done for Revenue and Web Traffic as well (I just remove the space for Web Traffic — Web Traffic_Current).And, we’re definitely going to want to have the whole range of data on the tab available to us.If you are looking for other Excel 2010-specific information that this post doesn’t cover, please leave a quick comment as to what the change/issue is that led you to the search.