Excel 2007 : Problem in a simple Inventory Sheet

**fitkhan** · 06-14-2012, 11:57 AM

Hi,
I shall be very thankful if someone helps me to manage my inventory sheet.
Attached is the inventory sheet.

Is it possible that when I sell a product and changes its status in column A to "Sold" this record automatically moves to sheet 2 (Sold items) and when the item is again in stock and I remove it's status "Sold" it automatically comes back to the main sheet.

and 2ndly how can I link the records in column B to the other sheets on this file. Like there are 3 type of parts in Column B (new,old and copy parts) and I need their copies on their relevant sheets as well (See the file)
if In future when I add 3 items 1 new , 1 old and 1 copy part to the full inventory sheet. they automatically copies to their relevant sheets on my excel work book.

Thanks a lot for reading.

**Sky188** · 06-14-2012, 01:32 PM

Good morning fitkhan,

To address the first part of your question,

There are many ways to do this, but two specifically stick out to me. First, and my personally preferred method, is changing the way you have column A and instead of having the word "Sold", insert an Form Control check box; after that, I would make a macro where you copy all the information on Sheet one and paste it to Sheet two, and another macro for vice versa, then attach those macro's to the status of the check box. Aka, you click the check box so it has a check mark in it, it runs the macro to cut everything from sheet 1 and pastes it to sheet 2, then when the checkbox is unchecked it cuts that same information from Sheet 2 and pastes it to Sheet 1.
The second is to have Form Control check boxes where the sold text is at and have it hide/unhide rows, and just have your first two sheets duplicates of eachother. Aka, Part 6860 is sold, the check box is clicked, that entire row is hidden, and the row on Sheet 2 with that exact same info is revealed.

For the second part of your question, I'm thinking there is a way to incure a search function that will look for a specific word, and you can run a macro off that. To ellaborate, run a search function that looks for the words "Copy Part" and when it finds something, copies and pastes it to the correct page. I'll look into this a bit more and get back to you!

**fitkhan** · 06-14-2012, 01:49 PM

Good Morning Sky188
Thanks So Much for you reply.
I am a very basic user of excel. I am trying to follow your instructions. Trying to make Form control check boxes.
I will let you know when I complete this part.
Thanks again.

**Sky188** · 06-14-2012, 01:57 PM

To make Form Control check boxes, (assuming you are using Excel 2007) go to the Developer tab, the left most panel will be code, and the middle one will be controls; in the controls pannel the left-most option is insert, and from there you can insert a handful of interactive tools like the form control check boxes. Once you have it placed on your document, right clicking it will allow you to assign it a macro.