Junaed-Ar-Rahman
About author
Md Junaed-Ar-Rahman, BSc, Biomedical Engineering, Bnagladesh University of Engineering and Technology, Bangladesh, has been working with the ExcelDemy project for 11 months. He has written over 15+ articles for ExcelDemy. Currently, he is working as Technical Content Developer in ExcelDemy project; writes unique articles as well as solves user problems. He participated in 2 specialized training programmes on VBA and Chart and Dashboard designing in Excel. He loves to solve any problem in unique way and explore new functions and fomulas of Excel. In future, he wants to explore more applications like Excel and gaining proficiency in those applications.
Junaed-Ar-Rahman
Hello Michiel,
Thanks for your question. If you want to add new products to the dataset and include them in your calculation, you will have to convert the range into a table. Then you will be able to add as many new products as you required by inserting new rows in the dataset.
On the other hand, if the products in the dataset are discontinued, you don’t need to modify any cells that contain the “Quantity” or “Revenue” values since we are not doing any arithmetic operation on them. For safety reasons, you can apply the IFERROR()function in all the cells where you are applying formula. For example, you would change the formula in Column G into IFERROR(E5/C5). The exception is the cells where we will calculate the sum of different values. For those cells, we will modify the formula of M11 into SUM(IF(ISNUMBER(M5:M10), M5:M10)).
Hopefully, you have got the desired answer.
Hello Terry Smith,
Thanks for your question. Most probably you are using a function like TODAY() function to find the first date in our worksheet. Then you have applied the Fill Handle feature. That’s why, the days are updating every day. The solution to this problem is to manually input the first date and then apply the Fill Handle feature.
Also, if your problem is different from the problem that I have assumed, then give us the Excel files. It will be convenient for us to solve the issue.
Dear HARIRI,
When you use the following command in your code, the prompt will not appear.
Application.DisplayAlerts = False
However, if you want to open a new file in the same name in the same folder, there may be two scenarios. You might be able to do that without any obstacles because you have a file with .xlsm extension only with that name and no .xlsx file with that name. But if you have two files with same name in same folder, one with .xlsm extension and another with .xlsx extension, then you cannot open a new Excel file in that folder. Prompt will appear to warn you that there is a folder with the same name and you will have to discard that action or have to modify the file name.