Inventory spreadsheet with dynamic charting

[Michael Gabriel]

I am wanting to develop a spreadsheet for the Ordering Manager at my work. In the file that I have attached you can see that I have already mapped out the days they will be ordering for the rest of the year. (the numbers inputted were randomly generated as I was trying to form pivot tables and charts myself)

They order on Mondays and Thursdays or Fridays (I want everything to auto update if they order on Fridays instead of Thursdays, because I assumed for all the ordering days to be on Monday and Thursdays). I want them to input what is on hand and how many are ordered for each day. I want to be able to pull from their file where the data is inputted to track how many parts are consumed from day to day, week to week, etc. I also need dynamic charting which shows # of parts on hand and # of parts ordered, which auto updates as they order throughout the year.

I have been trying to use pivot tables and offset formulas for the dynamic charting and I am simply stuck. Any help is greatly appreciated!

Thank you!

 

[Chihiro]

Hi,

The way your data is organized makes it impossible to use pivot.

To use a pivot table and chart, you need data organized into table format (I.E. 1 header for each column).

Also, merged cells = bad. See bobhc's post for reasons why.
http://chandoo.org/forum/threads/how-can-i-use-sumifs-or-countifs-in-merged-cells.24802/#post-149023

Personally, I'd recommend using Access instead of Excel for inventory management. But, there are some inventory tutorials out there for Excel as well as templates.

Some links.
http://indzara.com/2013/07/inventory-and-sales-manager-excel-template/
http://www.tidyforms.com/download/stock-inventory-control-template.html

 

[Michael Gabriel]

Chihiro, thank you. Access is not an option.

I got rid of the merged cells and tried to rearrange the format. I am still stuck, though.

I am wanting to graph: a) the parts on hand and b) parts ordered (for each day inputted, for each part).

I would like to maybe have each part # as a series.

 

[Chihiro]

There's a lot of different part# to track them as series and will make it too busy to get meaningful info from chart.

I'd recommend grouping parts by certain criteria and showing each group in separate chart.

For example: Grouping x .65 items and show graph, then have control that switch to another grouping, say x .125 items and update chart accordingly.

Alternately you can have target stock range and display item only when it falls below the target stock level. Thus indicating when you should order more.

Are you open to these type of set up?

 

[Michael Gabriel]

I have already considered grouping, but by the middle value.

The display for falling below a certain stock level would be a nice option, however at this time I am just looking to track the amount on hand and amount ordered for each day ordering is done.

 

[Chihiro]

Ok, I'm bit busy at the moment with work.

If no one else responds and if you can wait, I should have time this weekend to get you base layout with some controls.