+ Reply to Thread
Results 1 to 2 of 2

Tracking Inventory to Create Trends Analysis

  1. #1
    Registered User
    Join Date
    04-12-2012
    Location
    Florida
    MS-Off Ver
    Excel:Mac 2011
    Posts
    1

    Tracking Inventory to Create Trends Analysis

    Hey guys. I am tracking use of non-inventory items for a business, mainly bags. I have attached my work here and want to see if there is a way of locking certain cells that are referencing functions in the top two tables from changing when the numbers in the tables change based on a physical count twice a week.

    Here is a brief explanation of the sheet attached:

    The In-Store boxes column (B11-B13) is what will change twice weekly. I will physically have a count done of the boxes we have on hand. Cells B3-B5 are just those boxes listed previously multiplied by 250, the number of bags per box. Now, B20 is the baseline count. It is the first count in the process and therefore should remain locked forever. But it is a reference to cell B3. Once I do a second count, B11 will change causing B3 to change causing B20 to change. I want B21 to reflect the change in B3 after the count on April 14 but I want the value in B20 to remain the same so I can create a graph that shows or trends. Is this possible? Is there a way to lock just the values in B20 (and C20 and D20)? Then I want the graph to the right (which will turn into a line graph) to reflect each new data set I enter. For this, I could just manually extend the graph's area of data pulling to BXXXX-DXXX.

    Thanks for your help,

    Matt

    Bag Tracker.xlsx

  2. #2
    Forum Contributor
    Join Date
    09-13-2011
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    216

    Re: Tracking Inventory to Create Trends Analysis

    Hello cfknight07,

    I think the best way to do what you want to track historical data is to track your data like you are doing from cells b19 and down. then you can have your chart area in B10:b14 look up the last value for that list.

    You can look up and chart your data using the offset formula:

    Here is a tutorial:
    How-to Make a Dynamic Chart Using Offset Formula tutorial: http://www.exceldashboardtemplates.com/?p=688

    Let me know if this helps.

    Steve=True

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1