Excel Formula Changes When Save As

Question

Hi everyone! I have a question regarding Excel. Currently, I have version 2203, Build 15028.20160 with Microsoft 365 subscription. I have 2 separate Excel worksheets, the first one is for my price lists, and the second one is for my bill. So, I have data validation applied on the bill worksheet so that if I type an item name from my price list, I have the drop-down lists to show the name of things that I have in there. But, I need to save as the bill worksheet because there is many people's bill that I need to make. But every time that I save as the worksheet, the formula for my data validation changes to that bill worksheet that I just save as. I already protect the worksheet and it still changes. Is there any way so that when I save as the bill worksheet, the formula in my data validation workbook does not change?

Answer 1

Hello,

Thanks for querying in this forum.

According to your description, you have 2 separate Excel worksheets, it seems like when you Save As the bill worksheet, the bill worksheet name in the formula on the Item lists worksheet will change to the new bill worksheet name you just save as.

May I know if my understanding of the scenario is consistent with yours?

If yes, we also tested from our side. We tried the formula in the screenshot you provided, and we also tried another simple formula in our test worksheets. We found we can reproduce the same situation as you described. As soon as we save as the bill worksheet, it will change the bill worksheet name in formula in the Item lists worksheet.

Since we can reproduce same result by using different formulas, we're afraid it's expected behavior in Excel. When we save as a source worksheet, the formula in destination worksheet will always use the new worksheet name.

We deeply understand this affects your daily work. Very sorry for the inconvenience caused at your end.

Given this, we sincerely recommend and encourage you send feedback via File>Feedback or Help menu>Feedback within Excel. Feedback submit by this way directly goes to the related team. This is the best way to let Excel developers know your requirements and help improve the product. Thanks for your understanding.

At the same time, we will also feedback from our side.

In addition, we understand you may want to use Save As to save the bill worksheet to a new worksheet instead of simply Save. For a workaround, you may try following steps:

1.Before you Save as the bill worksheet, we suggest you go to the Item lists worksheet which contains the formula, save it and close it. (keep it closed when you save as bill worksheet)

2.Then do Save as for the bill worksheet.

3.Then reopen the Item lists worksheet.

You may see a message as below. Click Enable Content, then it will keep the original bill worksheet name and continue linking to the original bill worksheet.

Note: this way requires you keep the Item lists worksheet closed before you do Save as for the bill worksheet.

If there is something we misunderstood, feel free to point it out. We will continue helping you.

Thanks for your patience and understanding. Hope you are keeping safe and well.

Best Regards,

Tina

Answer 2

I'm not really sure why you don't want the formula to update, but the only ways I know of to prevent it are either to close the workbook with the formula while you are saving the other workbook under a different name, or to use two separate excel instances while you are working.

Answer 3

Dear Tina,

Yes, your understanding is similar to mine. Thank you for letting me know the steps to deal with this problem. It solves my problem, but it is not a very convenient way to solve it, with all due respect. I guess that I'm going to use this method for now, and I'm going to send feedback to the related team directly. I hope that they improve it in the future.

Best Regards,

Martin

Answer 4

Dear Rory,

I don't want the formula to update because I want to reuse the empty bill worksheet for the other customer and if I Save As the empty bill worksheet, then the data validation in the initial bill worksheet does not work. Also, thank you for your solution. It is the same as Tina's way, and it did solve the problem!

Best Regards,

Martin