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

	A	B	C	D
1		Product	Qty
2		APPLES	4
3		ORANGES	5
4		PEARS	2
5		BANANAS	6

<tbody>
</tbody>

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..

	A	B	C	D
1		PRODUCT	QTY
2		APPLES	3
3		PEARS	11
4		BANANAS	4
5

<tbody>
</tbody>

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

	A	B	C	D
1	PRODUCT	QTY	LOCATION
2	APPLES	4	SHELF 1
3	ORANGES	5	SHELF 2
4	PEARS	2	SHELF 3
5	BANANAS	6	SHELF 4

<tbody>
</tbody>

The issue I am having is that when the oranges disappear off the sheet 1 report my sheet 2 ends up looking like this...

	A	B	C
1	PRODUCT	QTY	LOCATION
2	APPLES	3	SHELF 1
3	PEARS	11	SHELF 2
4	BANANAS	4	SHELF 3
5			SHELF 4

<tbody>
</tbody>

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...

	A	B	C
1	PRODUCT	QTY	LOCATION
2	APPLES	3	SHELF 1
3	PEARS	11	SHELF 3
4	BANANAS	4	SHELF 4
5

<tbody>
</tbody>

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.