Chemical Inventory

Hi All,

I am currentley creating a chemical inventory spreadsheet in our department at work and all you have to do is put the date in that we receive the chemical and it works out the expiry date and every time you open the spreadsheet it works out whether chemicals have expired from the current date, and on the menu displays how many chemicals are in the database and how many have expired.

What I want to do is when you press the expiry Report Button, a search is started in columns E and G in the sheets (Flam, Fridge, Cupb and Bench) for any "EXP" cells (there are some in the attatched file). Then when it finds an "EXP" cell I want it to copy that entire row and put it into the Report sheet (The sheet that it currently goes to when you press the Expiry Report button from the menu), so at the end I want it to display a list of the chemicals in the Report sheet that have expired.

If anyone can help it will be much appreciated. Also I am quite new to macros and dont have much knowledge of viusal basic, which I assume will have to be used as I dont think you can do this with formulas can you? so please could you tell me exactly how to do this in simple steps. Thanks.

I have attatched my worksheet.

1) I added a DE/STD column to the two sheets it was missing from so all sheets have the same headers in row 5.

2) Then this macro goes into the Report SHEET module:

Please Login or Register  to view this content.

Basically, each time you bring up the Report onscreen it will collect all the data in again for you, so it's always current.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.