Changing table data

[Lefty099]

Hi all, a hopefully simple one for you.

I have a report which changes daily. That report is linked to sheet 1 on my spreadsheet. I want to transfer the data from sheet 1 column D to Sheet 2 column B automatically.

Once this is done I want to be able to manually type something into column C and everytime Sheet 1 updates, that manual typing stays relevant to its row.

eg. Sheet 2 D4= Hammer E4= $24.99
D5= Spanner E5= $12.99

Data updates overnight...

Sheet 2 D4= Hammer E4 = $24.99
D5= Screwdriver E5 =
D6 = Spanner E6 = $12.99

Same deal will need to apply if something gets sold and not on the list. eg, hammer gets sold. List would then need to be

Sheet 2 D4 = Screwdriver E4 =
D5 = Spanner E5 = $12.99

The inventory list will change every day, so I need anything I type into column E to be locked to the same row as the product it relates to.

I have no VBA experience so this is over my head.

I really hope this makes sense, so thanks in advance for your help.

 

[oldbrewer]

the inventory on sheet 2 cols D and E updates daily

is sheet 1 sales of items ?

 

[Lefty099]

the inventory on sheet 2 cols D and E updates daily

is sheet 1 sales of items ?

Sheet 1 is a full inventory list. If new stock comes in the list grows. If the stock level becomes 0 the list shrinks. The sales comes from a different sheet using vlookup.

 

[Lefty099]

So column d upates on sheet 1. When i manually type something in column e i need that to be relevant to the cell/product beside it. If the product in d moves up or down a row or 5, i need column e to move with it.

 

[oldbrewer]

so if one row is 5 oranges, and you sell 5 oranges, you want the oranges row in inventory sheet to disappear ?

 

[Lefty099]

Hi brewer, sorry if I hadnt explained it well enough I will try again and use some demo tables.

Sheet 1 is a full inventory report that updates each night when the business closes.

example 1 - Monday morning the inventory level is like this... Column B adn C are updated automatically off the report, Column D is a manual input of where the products are located

Sheet 1[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Product[/TD]
[TD]Qty[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]APPLES[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]ORANGES[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]PEARS[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]BANANAS[/TD]
[TD]6[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

During the day on Monday, we sell 1 apple, 5 oranges, 2 bananas and get 9 pears into stock.

So Tuesday Morning the inventory report looks like..

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]PRODUCT[/TD]
[TD]QTY[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]APPLES[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]PEARS[/TD]
[TD]11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]BANANAS[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Column B Transfers across to Sheet 2 and adjusts accordingly. And using Vlookup I am obtaining all other information I am needing from that report. The issue is on sheet 2 I have a column in which I manually type in the location of the products. (Column C below)

Sheet 2 example Monday Morning

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]PRODUCT[/TD]
[TD]QTY[/TD]
[TD]LOCATION[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]APPLES[/TD]
[TD]4[/TD]
[TD]SHELF 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]ORANGES[/TD]
[TD]5[/TD]
[TD]SHELF 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]PEARS[/TD]
[TD]2[/TD]
[TD]SHELF 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]BANANAS[/TD]
[TD]6[/TD]
[TD]SHELF 4[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The issue I am having is that when the oranges disappear off the sheet 1 report my sheet 2 ends up looking like this...
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]PRODUCT[/TD]
[TD]QTY[/TD]
[TD]LOCATION[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]APPLES[/TD]
[TD]3[/TD]
[TD]SHELF 1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]PEARS[/TD]
[TD]11[/TD]
[TD]SHELF 2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]BANANAS[/TD]
[TD]4[/TD]
[TD]SHELF 3[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]SHELF 4[/TD]
[/TR]
</tbody>[/TABLE]

Cell C3 which was relating to the Oranges has not changed.
It is removing the Data that is referenced off sheet 1 but it is leaving the manual data in place, which is giving me incorrect locations.
What I need it to do is move column C sheet2 up or down relevant to column A...

example of what I need sheet 2 to end up like...
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]PRODUCT[/TD]
[TD]QTY[/TD]
[TD]LOCATION[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]APPLES[/TD]
[TD]3[/TD]
[TD]SHELF 1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]PEARS[/TD]
[TD]11[/TD]
[TD]SHELF 3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]BANANAS[/TD]
[TD]4[/TD]
[TD]SHELF 4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any new products that appear should have a blank cell in Column C, and if a product disappears from column A, I need the entire Row contents relating to that product to disappear with it.

I sincerely hope this makes a bit more sense now. It is frustrating me to no end as I have used spreadhseets in the past where this happens and when it does, it is a great inventory control tool.

Thank you so much again for your help.