Delete complete row where first cell is linked to sheetname|General Excel Questions & Answers|Excel Forum|My Online Training Hub

Hi

I created a workbook based on several formulas and functions. I attached the document but it is in Dutch so I will try to explain the situation and my goal/problem. Most of the sheets are protected without password, sorry for that.

I started the workbook with a list of products that I used to create a number of worksheets with the name of every product. I then used the name of the worksheets to create a new list of products on the Index-sheet but added the hyperlink to the productname referring to its own worksheet. I used the GET.WORKBOOK method explained in Myndas video. Thats why the workbook is in a xlsm format. On every product-worksheet I then added a table to follow the stock of products left (with also the IN/OUT to be added manually on the date the transfer occures) 

Furthermore on the Index-worksheet in Column D, I added a column to put in manually the starting status in the beginning of the trimester. These are plain numbers, no formulas. In Column E on the Index-sheet, I also added a search function to return the last status of every product (with an INDIRECT formula).

This INDIRECT formula refers to Cell G4 on every product-sheet. This cell G4 gives me the last value of column E on the same sheet with a LOOKUP formula. On the top of every product-worsheet I added again the name of the product using a MID /FIND formula.   

Now the problem: if I delete a sheet (because we don't have the product anymore for example), the sheet disappears and on the INDEX-sheet in column B the name of the deleted product with the hyperlink is removed (rest of the cells in the column move up) BUT the other columns on the INDEX-sheet stay as they are with a wrong column D and wrong column E as a result. I tried to delete a product directly on the INDEX-sheet but this deletes the product name and hyperlink, giving me a blank cell but the other columns D and E shft one cell up again giving the wrong value for the product.

My question: is there a way to delete a sheet without messing up the values in column D and E on the INDEX sheet?       

Hopefully the explanations are somewhat clear! 

Regards

Tom