Excel - storage file -

Hello everybody,
Generally, I would like to create / modify a file that I place to run a micro production of electronic products. For this I need a file in which I will be able to record the purchase of electronic components and mark it quantitatively. How do you remove from the warehouse ready products for construction which I used components lying in the warehouse.
That's generally what I would like to get.
As for the tabs, in the tabs "Extr1 technology, etc." I want to enter what product range a given product consists of. And by clicking on product shipping, I want the quantity of the assortment included in the quantities entered in the technology to leave the warehouse.
I intend to have a maximum of a dozen products - therefore tabs with technology will be adequate.
Indexes for shipment would like to be only those entered in cells D3 in the tab "Extr1 technology, etc.".
Someone is able to help me in this endeavor, for processing I put a file "magazine" in which some things are done.

The base file I mentioned is under the link: https://www.elektroda.pl/rtvforum/topic2973819.html#14359769
I cannot return it directly because the extension is incorrect.

I would ask experts for help.

Know my good heart

Stock levels are created on the basis of entered documents, so "manual" intervention in the sheets DocumentsPZ and DocumentsWZ will "break" the states.

Stock levels are getting up-to-date but if something stuck, the button Restore states based on documents from the beginning will create a statement



There are some security instruments but I don't know if the workbook is fully idiotootproof. How to check this first before you start using the file seriously. Later, however, I would advise you to enter the data correctly, i.e. if there is a quantity, then enter a positive quantity and not letters or negative values.

I note that the empty cell at the quantity is zero for calculations.



I wrote the file in Excel2016 but it should work above E2007, it will get older.

fighter31 wrote:

Hi Mark! The file works great, there is much more than I wanted, I didn't even think about recording PW and PZ and I would know when it came and went! REVELATION.

Tell me such a thing, if the technology tab could be changed columns with rows so that it is easier to paste material and quantities with BOMA.
The second thing is whether in the magazine tab next to the status you can add a column with minima entered for each item from your finger and in case when the stock level drops below this value, the cell color will be highlighted in red (such as Kanban).

And one last thing, but I don't know if it can be overcome in excel so that in the case of zero or smaller than needed for production it is impossible to make a shipment and that there was an alert that it is not possible to carry out due to deficiencies.

I still have a question about the help tab - what is it for?

File attached
1. The columns with rows have been replaced - but I advise against pasting because then the protection against entering the incorrect name will not work. For Excel, a space is also a character and if the dictionary does not have the same name, the program will report a technology error.
30 assortments is not much to do by hand, the more that you can check everything.

2. The column with the minimum is in the Dictionary. and too small states "burn" in red both in the dictionary and in the magazine tab.

3. Quantitative safeguards are made but I am human and I could have been wrong somewhere so check for yourself if it works well. Test for yourself if you can produce something without states.

4. The help file is now used to check the raw materials needed to produce each of the 10 products selected in the production tab. Do not interfere with the operation of this sheet - you can hide it.

I mark it once again - I - the man wrote - and I could overlook / miss something. Before you start using the file seriously, test it in every direction.
I treat it as fun and I am not responsible if for some error in the code the inventory would not be correct and ultimately someone would be unpleasant because of it.

One more thing
The magazine as such is just a dynamic REPORT. Correction of data in this spreadsheet will not work in the long run. Because after pressing "refresh states" based on source documents (PW RW PZ and WZ) will correct any corrections.

If it is necessary to improve the stock level, it should be corrected in the source document (PW RW PZ or WZ)
The sheets have protection but I didn't password them so remove the protection - improve the data and turn protection on again


As I corrected the file, it seemed to me that this first file may have an error when checking the right amount of raw material during production (I did not include 10 times)

Thanks so much Mark!
I will play and let Excel know how it behaves.

Hello, I was looking for a small book storage program in a bookstore and generally this file meets my expectations. I have questions if I can add such options:
1) DELIVERY
a) Acceptance of PZ plus own text to enter
b) name hints from the data in the dictionary (because we are talking here about a few thousand names sometimes, hints would help significantly)

These two things would improve this file for me amazingly.

PS. Change of the number of PZ and WZ to 100 from 10.

marek003 wrote:

fighter31 wrote:

Hi Mark! The file works great, there is much more than I wanted, I didn't even think about recording PW and PZ and I would know when it came and went! REVELATION.

Tell me such a thing, if the technology tab could be changed columns with rows so that it is easier to paste material and quantities with BOMA.
The second thing is whether in the magazine tab next to the status you can add a column with minima entered for each item from your finger and in case when the stock level drops below this value, the cell color will be highlighted in red (such as Kanban).

And one last thing, but I don't know if it can be overcome in excel so that in the case of zero or smaller than needed for production it is impossible to make a shipment and that there was an alert that it is not possible to carry out due to deficiencies.

I still have a question about the help tab - what is it for?

File attached
1. The columns with rows have been replaced - but I advise against pasting because then the protection against entering the incorrect name will not work. For Excel, a space is also a character and if the dictionary does not have the same name, the program will report a technology error.
30 assortments is not much to do by hand, the more that you can check everything.

2. The column with the minimum is in the Dictionary. and too small states "burn" in red both in the dictionary and in the magazine tab.

3. Quantitative safeguards are made but I am human and I could have been wrong somewhere so check for yourself if it works well. Test for yourself if you can produce something without states.

4. The help file is now used to check the raw materials needed to produce each of the 10 products selected in the production tab. Do not interfere with the operation of this sheet - you can hide it.

I mark it once again - I - the man wrote - and I could overlook / miss something. Before you start using the file seriously, test it in every direction.
I treat it as fun and I am not responsible if for some error in the code the inventory would not be correct and ultimately someone would be unpleasant because of it.

One more thing
The magazine as such is just a dynamic REPORT. Correction of data in this spreadsheet will not work in the long run. Because after pressing "refresh states" based on source documents (PW RW PZ and WZ) will correct any corrections.

If it is necessary to improve the stock level, it should be corrected in the source document (PW RW PZ or WZ)
The sheets have protection but I didn't password them so remove the protection - improve the data and turn protection on again


As I corrected the file, it seemed to me that this first file may have an error when checking the right amount of raw material during production (I did not include 10 times)

Mr. Mark, I have a question for this file. Shouldn't the shipping tab also include manufactured products?

msk234 wrote:

Mr. Mark, I have a question for this file. Shouldn't the shipping tab also include manufactured products?

It should take into account as much as possible and at the beginning only included in the heat of corrections and facilitation I forgot to change dictionaries.

It should be fine now but ... please check. (I used to do it a long time ago and I don't remember all dependencies)

My question was a bit too fast because I came to the fact that I need to complete the dictionary. But in the meantime I had one more problem with the spreadsheet and despite attempts to edit the macro, I couldn't solve it. Namely, could it define the variables in such a way that they were not integers? I have a situation when for the production of a given product I use fractional parts from stock, e.g. a profile with a length of less than a meter or with a length of more than 1.5m.

marek003 wrote:

msk234 wrote:

Mr. Mark, I have a question for this file. Shouldn't the shipping tab also include manufactured products?

It should take into account as much as possible and at the beginning only included in the heat of corrections and facilitation I forgot to change dictionaries.

It should be fine now but ... please check. (I used to do it a long time ago and I don't remember all dependencies)

Mr. Marek, a small request, could you please extend the number of positions in product technology to 100. I have quite complex products and 30 is not enough for me.

Thank you very much in advance

Hello,
I wanted to ask Mr. Marek if this excel could also keep records with units (g, ml, kg, dm3, etc.)?
Wieslaw

wieslawprugar wrote:

Hello,
I wanted to ask Mr. Marek if this excel could also keep records with units (g, ml, kg, dm3, etc.)?
Wieslaw

It depends how you want to run this warehouse.

To tell the truth, this "warehouse" is unitless, or rather "piece", but it can be assumed that it will be also individual.

Ie: what you enter in the name of a given assortment - this will be the unit and its subsequent quantity.
It certainly does not provide for the total counting of individual assortments by units.


It can be like this ("artfully"):

So, for example, the name: "Apple juice 500ml", quantity: 6 - indicates that you have 6 pieces / units of juice 0.5l - (i.e. a total of 3 liters - but the program does not calculate this, the warehouse keeps only the amount of the given assortment and whether the assortment is 0, 5l or 5dm3 or 3g it does not matter to him).

Now, if the assortment is "Orange juice 1l" and the amount is also 6, you will have 6 liters of juice.
The next assortment after the juice can be, for example, "Raisins 35g" and their appropriate amount, for example 8. That is 8 pcs packages of raisins.

But this warehouse can also be run under the unit - except that you have to understand it yourself and to be sure, somehow simply name the assortment, for example:

name of the assortment: "Potatoes in bags [kg]", quantity: 98 it is logical that we have 98 kg of potatoes in bags.
Similarly:
name of the assortment: "Potatoes in bags a'25 [kg]", quantity: 98 so here we have a total of 98 kg potatoes in bags a'25
(It should be supposed to be 100 kg, because the bags according to the name "a'25" should contain 25 kg but if we weigh the bags and do not carry them in pcs, it is ok, because it may happen that on average four 25kil bags are missing in each of them 0.5 kg of potatoes, i.e. 98 kg in total). Because we have established that we run a warehouse in the units entered as the last ones in the name of the assortment.

The same in other units, e.g.
name of the assortment: "Juice in a barrel a'400 [l]", quantity: 1087, so we have 1087 liters of juice in some barrels, probably not fully filled

And here it is more difficult:
name of the assortment: "Raisins 35g [kg]", quantity: 8, so we have 8 kg of raisins in some packages. Here, however, I would add some packaging to the name of the assortment because it can actually be misleading, for example:
"Raisins 35g in a carton [kg]" or "Raisins 35g palletized [kg]"


I don't know if I explained clearly and if it will meet your needs.

Hello, Thank you for your explanation. This is understandable to me. I am looking for a solution that would allow, however, to run the units mainly due to production. My recipes require precision and, for example, I need to enter e.g. fractional parts of a gram. In addition, ultimately I also want to generate RW / PW on products with prices (e.g. warehouse in average-weighted prices) and this is probably too far-reaching change. Likewise, unit prices would have to be entered to 5 decimal places.
If such corrections were possible, I would be happy to discuss on priv?
Wieslaw

For such a thing, I suggest looking for a serious program, not a toy in excel, which at any time with the "stupid" one can destroy the historical data or even blur the data completely with the record.

Here you need something based on databases, not a spreadsheet.

Of course, I know professional solutions. But this is an excess of form over needs for my needs. I was looking to make it simpler on Excel. Nevertheless, thank you for the answers and the discussion. Greetings. Wieslaw