HELP! ANYBODY! KEep getting skipped over! I NEED HELP

[2clacaze]

I can't get my inventory to work the way I want it to. I work for a powder-coating company, so our inventory is dry, colored powder, tracked in pounds. Right now it looks something like this:

Sherwin Williams	BK01	35
	BK03	47
Rodda	Gloss Black	14
	Midnight	55
Cardinal	RAL 7011	6
	RAL 7112	83
	RAL 7003	17

<tbody>
</tbody>

The powder manufacturer is in the first column, with their respective colors ranked below each in the next column. The third column is the current weight, in pounds, on hand. I want to add another column to reflect daily powder usage, in pounds,

Sherwin Williams	BK01	35	7.5
	BK03	47
Rodda	Gloss Black	14	4
	Midnight	55	20
Cardinal	RAL 7011	6
	RAL 7112	83	6.5
	RAL 7003	17	0.5

<tbody>
</tbody>

And I'm fine to this point. Not all colors are used daily, and I don't subtract zero. But I want the form to update after I hit enter on each usage cell:

Sherwin Williams

BK01

35

7.5

<tbody>
</tbody>

*ENTER*

Sherwin Williams

BK01

27.5

<tbody>
</tbody>

I run into circular references when trying a =SUM('currentweight'-'usage')

This inventory is a mess! Someone please HELP!

 

[barjoman]

I have no doubt that someone else here can come up with a VBA solution to this. I am not well versed in VBA and can't help you with that.

However, I really think you should be reconsidering how you have structured your file. It looks to me like you want to be able to enter your usage (a transaction) and then show the remaining inventory for the item. Right now, your data (the existing inventory and the usage) are the same cells as your report (how much inventory is remaining). You should separate your data from your report.

Recommendation:
1) Create a separate sheet (call it data, or entries, or transactions, or whatever you believe is best)
2) Have the following columns: Date, Manufacturer, Colour, Weight
3) Start by entering a starting balance for each Manufacturer and Colour.
4) Then enter a line for each usage
5) Enter positive weights for increases to inventory (received a shipment) and negative weights for decreases in inventory (usage)
6) An example table is below.

Date	Manufacturer	Colour	Weight
xx/xx/xxxx	Sherwin Williams	BK01	35
xx/xx/xxxx	Sherwin Williams	BK03	47
xx/xx/xxxx	Rodda	Gloss Black	14
xx/xx/xxxx	Rodda	Midnight	55
xx/xx/xxxx	Sherwin Williams	BK01	-7.5
xx/xx/xxxx	Sherwin Williams	BK03	-7
xx/xx/xxxx	Rodda	Midnight	-10

<tbody>
</tbody>

Once you've done this, you can create a pivot table which will list every manufacturer and colour that you have available along with the remaining weight. Every time you update your data with a new entry, refresh the pivot table to view the updated weights.

If you'd like more help with this, I'd be happy to spend more time with you.

 

[chicagocomputerclasses]

The problem is that your idea of handling this may be flawed.

Let's say you type 7.5 and hit enter and the cell updates to 27.5. Now when you go back to that cell and hit enter again it will deduct another 7.5 out of your 27.5, so you are left with 20.

Please make a screenshot of your worksheet or use an html converter to show us what happens on your worksheet, this way we can find a macro solution for you. Otherwise use the suggestion from the above post.

 

[Irrelevant]

just create another column where you do the 35-7.5, and paste values the result back to the original column when you are done. There is a also a simple macro you could write for this but probably not necessary in this case. Also, as mentioned by previous post, its not a very good approach because you are losing information doing this. I would recommend creating a time series instead. Maybe a sheet that has cumulative values and daily usage values for each color for each workday would be a more useful tool for you (keeps history and lets you trend stuff later)

 

[barjoman]

I've sent you a private message with an email you can use to reach me. I would recommend editing your post above to delete your emails as they can be picked up by spam bots.

 

[barjoman]

I've created a working file which you can use moving forward. Click the link below to download it. Please note that the link is only active for 30 days.



The file contains two sheets.

The first sheet contains all of your transactional data. For every IN and every OUT, you would add a line with all of the appropriate details (Vendor, Name, etc). You have to fill in every column on every line, and you shouldn't have any blank lines. IN's would be positive numbers and OUT's would be negative numbers. Note that there is also a column for colour. You do not need to keep a separate sheet for each colour like in the screenshot you sent me. You can if you'd like to, but I would recommend to keep everything in the same place. Remember, this is just your data, not the report, so you shouldn't be formatting it as a report. No colour coding, blank lines, etc to make it more legible.

The second sheet contains the report. This list all of your items with the total weight (all of the positives and negatives added up). You can use the filters (they are called Slicers) that are on the page to filter by colour, vendor, etc. I've also included some slicers on the first sheet to allow you to filter your transactions if you'd like.

I hope this works for you.

 

[Peter_SSs]

I've sent you a private message with an email you can use to reach me.

Please refer to # 4 of the Forum Rules regarding taking questions off-forum.

 

[wideboydixon]

This is also breaking Rule #12 since I've posted on the "other" topic.

WBD

 

[barjoman]

Please refer to # 4 of the Forum Rules regarding taking questions off-forum.

The poster was unable to post a screenshot or attach a file (that post has been deleted). The off-forum part was simply to get that information. As you can see, I've posted my full response on the forum.

If this is STILL against the rules, please let me know how this should have been handled.

 

[2clacaze]

I also apologize for rule bending. I'm not sure how to show my work here. Every time I go to post a screenshot, the only option i get is to paste from a URL...