Locking manual data against variable data

[Lefty099]

Hi all. I hope the following makes sense and thanks in advance for your help.

I have a spreadsheet in which the data is automatically updated every day on sheet 1.
On sheet 2 column D from sheet 1 is copied across to sheet 2 into column B. Sheet 2 B2 =SHEET1!D1

Sheet 2, column C is a column in which I manually input the location of the item on the shop floor.

The problem I am finding is if I sell the last item of a particular model it disappears from sheet 1. This in turns removes the item from sheet 2 but leaves the location where it is.
What I need it to do is delete the entire row of data from sheet 2 if the product is no longer on the report on sheet 1.

Currently when the product disappears it moves all the cells below that product up to fill the void, meaning a product in cell B4 that was located in area 01 in the shop will move up to B3, but the location wont move with it.

I will put a couple of before and after examples below.

Sheet 2 - Shows initial page with stock and locations
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Product[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Fridge 1[/TD]
[TD]01[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]Fridge 2[/TD]
[TD]02[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]Fridge 3[/TD]
[TD]03[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]Fridge 4[/TD]
[TD]04[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Lets say that Fridge 2 gets sold so is no longer showing on the report list. 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][/TD]
[TD]Product[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Fridge 1[/TD]
[TD]01[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]Fridge 3[/TD]
[TD]02[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]Fridge 4[/TD]
[TD]03[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]04[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What I want it to look like:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Product[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Fridge 1[/TD]
[TD]01[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]Fridge 3[/TD]
[TD]03[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]Fridge 4[/TD]
[TD]04[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I hope this makes sense. Any assistance would be greatly appreciated as I have searched the net for days trying to get it sorted.

 

[Lefty099]

Hi all. I have been thinking constantly about this and think I know what needs to be done, however I have no idea how to do it.
What I think needs to happen is when Sheet 1 updates each day, a code of some sort needs to check if the information in column B sheet 2 is still on sheet 1 column D. If the data is missing from sheet 1 it deletes the relevant row on Sheet 2.
In my head it makes sense, hope it makes sense when you read it...

Thanks again for any help on this matter