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

	A	B	C
1		Product	Location
2		Fridge 1	01
3		Fridge 2	02
4		Fridge 3	03
5		Fridge 4	04
6

<tbody>
</tbody>

Lets say that Fridge 2 gets sold so is no longer showing on the report list. Sheet 2 ends up looking like this:

	A	B	C
1		Product	Location
2		Fridge 1	01
3		Fridge 3	02
4		Fridge 4	03
5			04
6

<tbody>
</tbody>

What I want it to look like:

	A	B	C
1		Product	Location
2		Fridge 1	01
3		Fridge 3	03
4		Fridge 4	04
5
6

<tbody>
</tbody>

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