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
Short Answer: No, if you manually type values in Index column D.
Instead, you should type these values in a manual table, where you should type the sheet name in a column and Starting Status in another column.
Your Index Column D should have a formula to get data from the Manual entries table, based on sheet name.
Thanks a lot, Catalin.
I did add a Helper-Tab with the manual inputs and a LOOKUP function to get these inputs in the Index table, like you suggested. Now it works just fine to delete a productsheet.
