Excel Help/Template designed

Hi,

I am looking to set up a spreadsheet (or for some lovely person to do it for me) for our goods in department, basically it will pull the relevant information from master sheet that the stock manager will be updating, the goods in guys can't have access to this, basically they select the supplier from a drop down menu, once the supplier has been selected the products that are linked with this supplier are then displayed, from there they enter in all information required; use by date, tempreture, quantity etc. All products have a minimum life that we will accept, if the product falls below this we will need to have an alert pop up box.

I have attached an example of what the masterdata will be like, we will need to be adding new suppliers, new products, updating information etc all the time.

I have also attached a spreadsheet that i found which looks along the lines of what i'm thinking - i just have no clue on how to get there?? Anyone out there who can help??

AttachmentSize
goods in master example.xls19 KB
Nick's picture

Excel Help/Template designed

hi..

your first attachment contains macros that are run in a VBA module that is protected.
- If you do not know the source, these macros can do damage to your computer, so under no circumstances do I recommend running or downloading sheets like this without first opening in SAFE mode, and checking the code
More info here:
OpenExcel Safely

- I have disabled the download of that file from my site in case it is malicious.

Now, to your problem..

To flag something, the best way is to use Conditional formatting
Check out my tip on Conditional formatting
- Conditional formatting can change the colour of your cells dependent on the value of another cell, so in this case you can easily flag the products that do not comply to your minimum life requirements.
- In terms of your problem of sharing the information out, I would recommend a simple solution first and see if that works:
- Have a single spreadsheet owner who has the file open on a share all the time.
- When other people open the file, it will be opened read only which means that they will not be able to modify it.
- When the owner updates and saves the file, changes can be picked up by the users when they close and reopen.
- The owner should be the one communicating the fact that they have updated the file via email or chat...