Autopopulate from one worksheet to another

[AcornNut]

Hello,
I'm very new to Excel (2010). I'm trying to figure out how to manage an inventory by keeping track of when an item is used, expired, or damaged and autopopulate that data on a second worksheet.

For example:

Column A is an order code ("offsuppencils")
Column B is the item description ("pencils") (column c is merged with column B)
Column D is expiration dates
Column E is the quantity needed to be in supply
Columns F through J are the actual quantity on hand for each week of the month (column J being for months
with 5 weeks).
I have it set to highlight any cell in columns F through J that are less than the quantity in Column E (same
row)(Conditionally formated)

What I need to do is for any item that needs to be ordered, that item's description (column B) and order code (column B) to autopopulate on a second worksheet:

Order code to go in first blank row (B24) (columns B and C are combined and columns D and E are combined) and the item description into same row (D24). Any subsequent items that need to be ordered would autopopulate the next blank row, and so on.

As if that's not bad enough, it would be great if I could control what column the quantity to be ordered will go in (used, expired, damaged) (columns H, I, and J on the second work sheet).

I'm told this would require some lengthy VBA code writing.

Any help would be greatly appreciated.

 

[AcornNut]

OK...This may be simpler:

Sheet1
Quantity
ITEM QUANTITY EXPIRES Expiring Week1 Week2 Week3 Needed
SOUP 2 in 2 months 1 2 2 1 1
BREAD 3 in 1 month 2 3 3 3 0
MILK 2 in 1 year 2 2 1 1 1
CHEESE 4 in 4 months 4 4 1 2 2
JUICE 2 in 2 weeks 1 2 2 2 0
FRUIT 3 in 1 week 1 3 3 2 1

Sheet2
Example: Item Used Expired
SOUP 1 1
BREAD 2
JUICE 1
FRUIT 1 1
MILK 1
CHEESE 2


1. I need to have the items expiring in 2 months or sooner to autofill the "Item" row and the quantity in
the "Expired" colomn (moving down automatically to the next available row).
2. I need any cell in the "Needed" column that is >0 to do the same in the "Used" column.

 

[AcornNut]

Here is a sample of the format that is currently being used...

******** src="https://onedrive.live.com/embed?cid...51A78B5A22E0!936&authkey=ABocD1RRT3AkCT8&em=2" width="402" height="346" frameborder="0" scrolling="no">*********>

Any help would be GREATLY appreciated!!