Building a stock take system

[Benji_Ho]

I'm re-working on a stock take system within Excel, The system I already have in place is workable but we are going to open up more nights (we are a nightclub) and I want to streamline the stock take procedure to minimise the workload.

The current system has 1 file, with 2 sheets within it. It's simple and easy but nothing is referenced to each other so there's a lot of different pricing on the same product or when I add new stock items I end up copying and pasting a lot of formulas.

The new system I have (hopefully) worked out should iron out any human errors when inputting data. The new system have the following files: Master Stock Sheet (This contains my cost price and retail price for all the days), and Black Stock Take Sheet (This contains my opening count, closing count, sold, GP calculator etc). The problems I'm running into at the moment are the following:

• After adding new products in, the previous week will still reference to the same cell but it will display false data, is there a way around this or will I need to save as PDF after each week?
• #div/0 keeps showing up for my GP sheet, if I put 0 using =IFERROR it will put a false GP for my weekly GP Average of that product
• Is there a way to reference Last week's closing stock to the Opening stock for this week without having to change the formula every week.

Also any general tips would be much appreciated.

Thanks in advance!