Entering data for an inventory list which is dynamic causes issues when the list changes

[Stevenmpeters]

I have an inventory list which has data for every day counted for the past year. Like this:

................. 04/28/2013........... 04/29/2013..........04/30/2013
Brick.................5......................... 5 ........................4
Blocks...............3.........................22........................16



When a new product is added/taken off to the master list the inventory names change but not the old data, so the the data will no longer line up.

..................04/28/2013............04/29/2013............04/30/2013
Brick..................5..........................5..........................4
Castable.............3.........................22........................16
Blocks................0..........................0..........................0


The inventory for the Blocks is now read under the Castable line, and the option to just manually make them line up each time is out. I need this old data to "follow" the names. Also, I need to the inventory names to remain dynamic because the same list is referenced in many other sheets, and it changes frequently. To be honest, I don't think there is a way to make them "follow" without a VBA (which also isn't an option unfortunately), but I figured I'd ask here first.

Thanks for the help,
Steven

[Melvinrobb]

Would you be able to upload a sample workbook? I think this can be solved by seeing your overall structure, and determining how you are updating the list of products, and how the inventory names change (is it dynamic, or manual....)

[Stevenmpeters]

Goal is to be able to add an item between, say 21, and 22 on the list sheet, and have it update the receipts sheet while still having the data line up in the receipts sheet. I'm also up for totally different ways of accomplishing the same goal. I need to have a master list update many sheets in this way. And the biggest thing is it needs to not use VBA and be rigid, since I'm designing this sheet for my company, so that it can be used by several people with no excel experience. Also, I hope I didn't break it by taking out all of our inventory data and using fake stuff - I did it quickly.

Thank you

[Melvinrobb]

The attached file will atleast show the theory behind what I suggest you do moving forward.
The Historical Data tab should virtually never be altered.
The Master List tab is where new products could be entered. the formula in the table identifies if the product exists in historical data and displays the number if applicable. When a new item is entered, the formula displays nothing, as their is no historical data to use.
Row 4 and 8 of the "Master List" tab are blank, but the other rows automatically adjusted when these prodcuts were added.

[Stevenmpeters]

Thank you so much for reply,

Wouldn't this solution mean I couldn't add new data each day?

[Melvinrobb]

I am unable to tell HOW you add data. Do you mean a $ for the date for a new product code? Would this ever happen, as these dates are in the past?
It is very difficult to understand the process you go throuugh to add new products in, and how exactly it is not working.
If your product names are dynamic, why are the columns to the right of it not also dynamic?

[Stevenmpeters]

Yeah, I thought I did a poor job explaining this. Allow me to try again.

On my original sample sheet, the inventory list tab contains the master inventory list in columns A-F which I want to populate several other sheets/reports. Each day I need to record the current inventory and receipts for each inventory item. So tomorrow, I will fill in the next column at the end of the receipts and inventory sheets for 05/01/13 (or since this data wasn't up to date, it would be 04/19 in this case).

Then, when we bring in a new inventory item, we'll insert a new row on the master list somewhere within the list. This is currently set to automatically change the inventory list on the receipts sheet, but it's also when the issue of the inventory list and the receipt data not lining up appears since the data next to the list doesn't move accordingly. It would be very simple to just manually move the data to make it line up, but with who will be using this sheet, that isn't an option.

It may become more clear if I say the actual goal more broadly. I need to enter two values every day for every product, but having two separate lists of inventory has caused problems/inconsistencies in the past and so I need there to be only one master inventory list which these sheets both use.

[Stevenmpeters]

So there has to be a dynamic list with non-dynamic data next to it. As I said earlier, I assumed this wasn't possible, but I don't know how else to accomplish my goal.

[Melvinrobb]

I definitely have a better understanding of your issue.
A table that is HALF dynamic, HALF manual is definitely vulnerable to issues like this.

Question: You are the only one entering data into this spreadsheet? Every other user is simply viewing the data?
If that is the case, I would suggest having one table you enter ALL of the data in: The Inventory List & and Inventory Receipts.
You could then have the Inventory List and the Inventory Receipts on seperate tabs, but be COMPLETELY dynamic, with no issues of rows being inserted in one table, but not the other.

Let me know if this sounds promising, and I can provide an example of the suggested solution given your scenario.

[Stevenmpeters]

Good idea. I'm designing this sheet since I'm leaving my company and my replacement(s) are inexperienced with excel. I understand how I can make the dynamic sheets based off of a sheet with both data points. It's the non dynamic sheet that I wouldn't know how to design without it being a mess. Since I already have almost a years worth of data for both the inventory count and the receipts, and I need the information listed by day day, I'm not sure how I could design the sheet in which one list of material has two pieces of data in line with it. Any thoughts?

[Melvinrobb]

Here is what I would use.

If you need to insert a row, insert two rows, and copy the formulas in A:C down. The summary tables would be dynamic and automatically show the new items.

[Stevenmpeters]

Brilliant. This will work well with a filter on the Inventory/Receipt column. Thank you so much for your help!

Steven

[Melvinrobb]

I DEFINITELY like the filter idea. Simply eliminate the need for any formulas, or additional tabs.
Simple is better. Or, more specifically, taking advantage of the available excel features is better.